将1010Data专有XML查询语句转换为标准SQL的技术求助

需求说明 #

昨日发起提问时未附上相关代码，问题在补充内容前就被关闭，因此本次重新提问：需要将下述专有1010Data XML代码转换为标准SQL，目前我已能识别部分逻辑，但处理子查询部分时存在困难，我自行尝试编写了部分子查询的SQL转换代码，烦请各位帮忙校验并完成完整转换。

原始1010Data XML代码 #

<library>
    <block name="create_hierarchy" lk1="active" lk2="inactive">
        <willbe name="{@var3}" value="g_first({@var1};{@lk1};g_cnt({@var1} {@lk1};{@lk1});{@var2})"/>
        <willbe name="{@var4}" value="g_first({@var1};{@lk2};g_cnt({@var1} {@lk2};{@lk2});{@var2})"/>
        <willbe name="{@var5}" value="ifnull({@var3};{@var4})" label="{@var6}"/>
        <colord hide="{@var3},{@var4}" hard="1"/>
    </block>
    <block name="create_product_master">
        <sel value="latest=1"/>
        <willbe value="active=0" name="inactive"/>
        <insert block="create_hierarchy" var1="rollup_product_code" var2="prod_type_code"  var3="prod_type_code_a"  var4="prod_type_code_b"  var5="prod_type_code_c"  var6="Subcategory"/>
        <insert block="create_hierarchy" var1="prod_type_code_c"    var2="prod_type_desc"  var3="prod_type_desc_a"  var4="prod_type_desc_b"  var5="prod_type_desc_c"  var6="Subcategory Description"/>
        <insert block="create_hierarchy" var1="prod_type_code_c"    var2="subclass_code"   var3="subclass_code_a"   var4="subclass_code_b"   var5="subclass_code_c"   var6="Category"/>
        <insert block="create_hierarchy" var1="subclass_code_c"     var2="subclass_desc"   var3="subclass_desc_a"   var4="subclass_desc_b"   var5="subclass_desc_c"   var6="Category Description"/>
        <insert block="create_hierarchy" var1="subclass_code_c"     var2="class_code"      var3="class_code_a"      var4="class_code_b"      var5="class_code_c"      var6="Minor Department"/>
        <insert block="create_hierarchy" var1="class_code_c"        var2="class_desc"      var3="class_desc_a"      var4="class_desc_b"      var5="class_desc_c"      var6="Minor Department Description"/>
        <insert block="create_hierarchy" var1="class_code_c"        var2="department_code" var3="department_code_a" var4="department_code_b" var5="department_code_c" var6="Major Department"/>
        <insert block="create_hierarchy" var1="department_code_c"   var2="department_desc" var3="department_desc_a" var4="department_desc_b" var5="department_desc_c" var6="Major Department Description"/>
    </block>
</library>
<base table="sales_line"/>
<sel value="(store_code=850)"/>
<sel value="(date_id<20210925)"/>

        <link table2="dim.latest_prod_dim" col="product_code" col2="product_code" keepcols="1"/>
              <tabu breaks="store_code,prod_type_code" label="Tabulation"/>

        <link table2="sales_line" col="store_code,prod_type_code" col2="store_code,prod_type_code" cols="avg_days_between_baskets,std_days_between_baskets,lookup_days_3">
        
            <sel    value="(store_code=850)"/>
            <willbe  name="date_time" value="datetime(date_stamp;time_stamp)" format="type:ansidatetime"/>
            <link  table2="dim.date_dim" col="date_id" col2="date_id" cols="fiscal_week"/>
            <sel    value="(fiscal_week>=202037)"/>
            <sel    value="(fiscal_week<=202138)"/>
            
            <link  table2="dim.latest_prod_dim" col="product_code" col2="product_code" keepcols="1"/>
            <tabu breaks="store_code
                         ,prod_type_code
                         ,rollup_product_code
                         ,basket_id" label="Tabulation">
                <tcol fun="avg" name="avg_date_time" source="date_time" label="Average Date Time" format="type:ansidatetime"/>
                <tcol fun="sum" name="sum_sales" source="grs_sales" label="Gross Sales $"/>
            </tabu>

            <sel      value = "(sum_sales>0)"/>
            <willbe   name  = "last_sold" label="Last Sold Date" value="g_rshift(store_code,prod_type_code,rollup_product_code;;avg_date_time;avg_date_time;-1)" format="type:ansidatetime"/>
            <willbe   name  = "days_between_baskets" label="Days Between Baskets" value="avg_date_time-last_sold" format="dec:4"/>
            <sel      value = "(days_between_baskets>0)"/>
            <willbe   name  = "basket_count" value="g_cnt(store_code,prod_type_code,rollup_product_code;)"/>
            <willbe   name  = "ntiles"       value="g_ntile(store_code,prod_type_code,rollup_product_code;basket_count>=36;days_between_baskets;days_between_baskets;10)"/>
            <sel      value = "(ntiles<>1 10)"/>
            <tabu     breaks="store_code,prod_type_code" label="Tabulation">
                <tcol fun="avg"     name="avg_days_between_baskets" source="days_between_baskets" label="Subcategory Average Days Between Baskets"/>
                <tcol fun="median"  name="med_days_between_baskets" source="days_between_baskets" label="Subcategory Median Days Between Baskets"/>
                <tcol fun="std"     name="std_days_between_baskets" source="days_between_baskets" label="Subcategory Standard Deviation Days Between Baskets"/>
            </tabu>
            <willbe name="lookup_days"   label = "Subcategory Lookup Days Raw"        value = "avg_days_between_baskets+(std_days_between_baskets*1.64485362695147)"/>
            <willbe name="lookup_days_2" label = "Subcategory Lookup Days Rounded"    value = "round(lookup_days;1)"/>
            <willbe name="lookup_days_3" label = "Subcategory Lookup Days Rounded Up" value = "int(if(lookup_days>lookup_days_2;lookup_days_2+1;lookup_days_2))"/>

        </link>
        
        <link table2="dim.latest_prod_dim" col="prod_type_code" col2="prod_type_code_c" keepcols="1">
            <insert block="create_product_master"/>
            <tabu breaks="department_code_c,department_desc_c,class_code_c,class_desc_c,subclass_code_c,subclass_desc_c,prod_type_code_c,prod_type_desc_c" label="Tabulation"/>
        </link>
                                    
        <link table2="dim.latest_prod_dim" col="department_code_c" col2="department_code" cols="department_max,department_default">
            <tabu breaks="department_code"/>
            <willbe name="department_max" label="Department Maximum Lookup Days" value="int(if(department_code=-1;90;department_code=1;6;department_code=2;35;department_code=3;90;department_code=4;21;department_code=5;15;department_code=6;21;department_code=7;21;department_code=10;10;department_code=13;21;department_code=16;21;department_code=18;14;department_code=19;90;department_code=28;90;department_code=999;90;-1))"/>
            <willbe name="department_default" label="Department Default Lookup Days" value="int(if(department_code=-1;1;department_code=1;1;department_code=2;6;department_code=3;12;department_code=4;2;department_code=5;3;department_code=6;3;department_code=7;2;department_code=10;3;department_code=13;5;department_code=16;9;department_code=18;5;department_code=19;15;department_code=28;1;department_code=999;1;-1))"/>
        </link>
        <willbe name="lookup_days_4" label="Subcategory Lookup Days With Department Maximum" value="if(lookup_days_3<=department_max;lookup_days_3;department_max)"/>
        <willbe name="lookup_days_5" label="Final Subcategory Lookup Days" value="ifnull(lookup_days_4;department_default)"/>
        <colord cols="store_code,department_code_c,department_desc_c,class_code_c,class_desc_c,subclass_code_c,subclass_desc_c,prod_type_code_c,prod_type_desc_c,avg_days_between_baskets,std_days_between_baskets,lookup_days_3,lookup_days_5"/>
        <sel value="department_code_c<>NA"/>
        <ignore newop=""/>

已尝试的子查询SQL代码 #

;WITH TransactionalData AS (
  SELECT  [S].[TRX_DATE]                                                                                                        AS [TRX_Date]
       ,  [S].[STORE_CODE]                                                                                                      AS [Store_Code]
       ,  [P].[Subcategory]                                                                                                     AS [Subcategory]  
       ,  [P].[Subcategory_Description]                                                                                         AS [Subcategory_Description]
       ,  [P].[Rollup_Product_Code]                                                                                             AS [Rollup_Product_Code]
       ,  COUNT(DISTINCT [S].TRX_ID)                                                                                            AS [TRX_Count]
       ,  LAG([S].[TRX_DATE], 1) OVER (PARTITION BY P.[Rollup_Product_Code] ORDER BY S.TRX_DATE)                                AS [Last_Sold_Date]
       ,  DATEDIFF(DAY,LAG([S].[TRX_DATE], 1) OVER (PARTITION BY P.[Rollup_Product_Code] ORDER BY S.TRX_DATE),[S].[TRX_DATE])   AS [Days_Between_Baskets]
    FROM  [SALE_LINE]              S
    JOIN  [dim_Product_Latest]     P
      ON  [P].[PRODUCT_CODE] = [S].[PRODUCT_CODE]
   WHERE  [S].TRX_DATE
 BETWEEN  '2020-09-25' 
     AND  '2021-09-26'
     AND  [S].STORE_CODE   = '850'
     AND  [S].SALE_NET_VAL > 0
GROUP BY  [S].[TRX_DATE]
       ,  [S].[STORE_CODE]                   
       ,  [P].[Subcategory]                   
       ,  [P].[Subcategory_Description]       
       ,  [P].[Rollup_Product_Code]           
)


  SELECT  [T].[Days_Between_Baskets]
       ,  [T].[Rollup_Product_Code]
       ,  NTILE(10) OVER (ORDER BY t.[Store_Code],[T].[Subcategory],[T].[Days_Between_Baskets]) AS [ntiles] 
    FROM  TransactionalData T
GROUP BY  [T].[Store_Code]
       ,  [T].[Subcategory]
       ,  [T].[Days_Between_Baskets]
       ,  [T].[Rollup_Product_Code]

现有代码校验修正点 #

• 分区逻辑错误：1010Data中g_rshift（等价于SQL的LAG）的分区键为store_code、prod_type_code、rollup_product_code，原SQL仅按Rollup_Product_Code分区，会出现跨店铺、跨品类的日期偏移错误
• 分箱逻辑错误：1010Data中g_ntile是在store_code、prod_type_code、rollup_product_code分组内做10分箱，且仅当分组内交易篮数≥36时执行，同时要排除分箱结果的第1、10分位值，原SQL全局分箱不符合要求
• 聚合维度遗漏：1010Data第一层聚合是按store_code、prod_type_code、rollup_product_code、basket_id分组聚合单篮销售和交易时间，原SQL按交易日期聚合缺少篮子维度
• 过滤条件不全：原XML通过财周限定了统计范围为2020财年第37周到2021财年第38周，同时要求店铺号为850，原SQL日期范围没有对齐财周规则

完整转换后的标准SQL #

WITH 
-- 篮子级聚合：对应XML中第一层tabu按store_code、prod_type_code、rollup_product_code、basket_id分组
BasketLevelAgg AS (
    SELECT
        s.store_code,
        p.prod_type_code,
        p.rollup_product_code,
        s.basket_id,
        AVG(CAST(CONCAT(s.date_stamp, ' ', s.time_stamp) AS DATETIME)) AS avg_date_time,
        SUM(s.grs_sales) AS sum_sales
    FROM sales_line s
    JOIN dim.latest_prod_dim p ON s.product_code = p.product_code
    JOIN dim.date_dim d ON s.date_id = d.date_id
    WHERE s.store_code = 850
      AND s.date_id < 20210925
      AND d.fiscal_week BETWEEN 202037 AND 202138
    GROUP BY s.store_code, p.prod_type_code, p.rollup_product_code, s.basket_id
    HAVING SUM(s.grs_sales) > 0
),
-- 计算篮间隔：对应XML中g_rshift算上次销售时间、days_between_baskets
BasketIntervalCalc AS (
    SELECT
        *,
        LAG(avg_date_time, 1) OVER (PARTITION BY store_code, prod_type_code, rollup_product_code ORDER BY avg_date_time) AS last_sold,
        DATEDIFF(DAY, LAG(avg_date_time, 1) OVER (PARTITION BY store_code, prod_type_code, rollup_product_code ORDER BY avg_date_time), avg_date_time) AS days_between_baskets,
        COUNT(*) OVER (PARTITION BY store_code, prod_type_code, rollup_product_code) AS basket_count
    FROM BasketLevelAgg
    QUALIFY DATEDIFF(DAY, LAG(avg_date_time, 1) OVER (PARTITION BY store_code, prod_type_code, rollup_product_code ORDER BY avg_date_time), avg_date_time) > 0
),
-- 分箱过滤：对应XML中g_ntile、排除第1和10分位
NtileFilter AS (
    SELECT
        *,
        CASE WHEN basket_count >=36 THEN NTILE(10) OVER (PARTITION BY store_code, prod_type_code, rollup_product_code ORDER BY days_between_baskets) ELSE NULL END AS ntiles
    FROM BasketIntervalCalc
),
-- 品类级指标计算：对应XML中第二层tabu算avg、std、lookup_days系列
SubcategoryMetrics AS (
    SELECT
        store_code,
        prod_type_code,
        AVG(days_between_baskets) AS avg_days_between_baskets,
        STDEV(days_between_baskets) AS std_days_between_baskets,
        AVG(days_between_baskets) + (STDEV(days_between_baskets) * 1.64485362695147) AS lookup_days_raw,
        ROUND(AVG(days_between_baskets) + (STDEV(days_between_baskets) * 1.64485362695147), 1) AS lookup_days_2
    FROM NtileFilter
    WHERE ntiles NOT IN (1,10)
    GROUP BY store_code, prod_type_code
),
-- 计算lookup_days_3向上取整
SubcategoryLookupDays AS (
    SELECT
        *,
        CASE WHEN lookup_days_raw > lookup_days_2 THEN CAST(lookup_days_2 + 1 AS INT) ELSE CAST(lookup_days_2 AS INT) END AS lookup_days_3
    FROM SubcategoryMetrics
),
-- 商品层级维度：对应XML中create_product_master块生成的层级字段
ProductHierarchy AS (
    SELECT DISTINCT
        prod_type_code_c AS prod_type_code,
        prod_type_desc_c AS prod_type_desc,
        subclass_code_c