背景

目前有一个诉求，想把业务计算完毕后的数据，属于格式化的数据，数据属性值比较固定🧷，以为动态表单的样式展示，但是展示的方式比较多，（涉及到各种计算方式，打平，聚合，行转列等，，),如果使用业务代码来处理，可配置性比较差，毕竟用户的行为具有不确定性，最后使用下面这种方式，只需维护一个map配置表头，和脚本内容。

import sqlite3

def convert_to_sql(string_array, business_meaning):
    # 解析业务含义
    fields = business_meaning.split('#')
    # 创建一个 SQLite 内存数据库
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    # 创建一个表
    create_table_query = "CREATE TABLE data ({})".format(', '.join(fields))
    cursor.execute(create_table_query)
    # 插入数据
    for string in string_array:
        values = string.split('#')
        # 使用参数化查询
        insert_query = "INSERT INTO data ({}) VALUES ({})".format(', '.join(fields), ', '.join(['?'] * len(values)))
        cursor.execute(insert_query, values)
    # 提交更改
    conn.commit()
    # 执行查询
    select_query = '''
SELECT 
    type,
    orders,
    GROUP_CONCAT(top10) AS top10,
    GROUP_CONCAT(top100) AS top100
FROM 
    (
    SELECT 
        type,
        orders,
        pit,
        CAST(SUM(CASE WHEN pit = 'top100' THEN CAST(d AS REAL) END) as REAL)*100 / SUM(CASE WHEN pit = 'top100' THEN m END) AS top100,
        CAST(SUM(CASE WHEN pit = 'top10' THEN d END) as REAL)*100 / SUM(CASE WHEN pit = 'top10' THEN m END) AS top10
    FROM 
        data
    GROUP BY 
        type, orders, pit
    ) AS subquery
GROUP BY 
    type, orders;
'''
    cursor.execute(select_query)
    # 获取查询结果的列名
    columns = [description[0] for description in cursor.description]
    # 获取结果
    results = cursor.fetchall()
    # 输出结果
    dict_results = []
    for row in results:
        row_dict = {}
        for i, column in enumerate(columns):
            row_dict[column] = row[i]
        dict_results.append(row_dict)
    print(dict_results)  # 打印以字典形式表示的结果
    # 关闭连接
    conn.close()

string_array = ["1#base#有序#top10#23#90", "2#test#有序#top100#45#68",
                "1#base#有序#top10#23#90", "2#test#有序#top100#45#68",
                "1#base#无序#top10#23#90", "2#test#无序#top100#45#68",
                "1#base#无序#top10#23#90", "2#test#无序#top100#45#68"
                ]
business_meaning = "mid#type#orders#pit#d#m"

convert_to_sql(string_array, business_meaning)