SQL中LAG函数使用及百分比计算问题与天数验证

问题背景 #

现有Employee表结构如下：

Employee(linked_lylty_card_nbr, prod_nbr, tot_amt_incld_gst, start_txn_date, main_total_size, tota_size_uom)

表中具体数据：

linked_lylty_card_nbr, prod_nbr, tot_amt_incld_gst, start_txn_date, main_total_size, tota_size_uom
1100000000006296409 83563-EA    3.1600  2021-11-10                  500.0000    ML
1100000000006296409 83563-EA    2.6800  2021-11-20                  500.0000    ML
1100000000001959800 83563-EA    2.6900  2021-12-21                  500.0000    ML
1100000000006296409 83563-EA    3.1600  2021-12-30                  500.0000    ML
1100000000001959800 83563-EA    5.3700  2022-01-14                  500.0000    ML
1100000000006296409 83563-EA    2.6800  2022-01-16                  500.0000    ML
1100000000001959800 83563-EA    2.4900  2022-01-19                  500.0000    ML
1100000000006296409 83563-EA    3.4600  2022-02-26                  500.0000    ML
1100000000006296409 607577-EA   3.9800  2022-05-26                  500.0000    ML
1100000000006296409 607577-EA   3.9800  2022-06-11                  500.0000    ML
1100000000001959800 83563-EA    3.9800  2022-06-14                  500.0000    ML
1100000000001959800 83563-EA    3.9800  2022-06-24                  500.0000    ML
1100000000006296409 607577-EA   4.4600  2022-07-30                  500.0000    ML
1100000000001959800 83563-EA    4.0100  2022-08-02                  500.0000    ML
1100000000001959800 83563-EA    4.0100  2022-09-01                  500.0000    ML
1100000000006296409 607577-EA   3.9800  2022-09-08                  500.0000    ML

需求是计算每位客户每次访问的体积变化百分比，以及回访间隔天数与当前体积的比值。原SQL查询如下：

SELECT 
  linked_lylty_card_nbr, 
  prod_nbr, 
  start_txn_date, 
  main_total_size, 
  total_size_uom, 
  (
    main_total_size - LAG(main_total_size, 1) OVER (
      PARTITION BY linked_lylty_card_nbr 
      ORDER BY 
        start_txn_date
    )) / main_total_size AS change_in_volume_per_visit, 
  (
    start_txn_date - LAG(start_txn_date, 1) OVER (
      PARTITION BY linked_lylty_card_nbr 
      ORDER BY 
        start_txn_date
    )) / main_total_size AS change_in_days_per_visit 
FROM 
  Employee
ORDER BY 
  linked_lylty_card_nbr, 
  start_txn_date

执行后发现：

• 当main_total_size从500变为1000时，change_in_volume_per_visit计算结果为0.5，但预期应为1（100%）。
• 需要验证change_in_days_per_visit的计算正确性。

错误修正与验证 #

1. 体积变化百分比修正 #

原公式错误地使用当前体积作为分母，正确的体积变化百分比计算应该基于上一次访问的体积，公式为：

(当前体积 - 上一次体积) / 上一次体积

修正后的SQL中，将change_in_volume_per_visit的分母替换为LAG(main_total_size, 1) OVER (...)，同时为避免除零错误，添加NULLIF处理：

SELECT 
  linked_lylty_card_nbr, 
  prod_nbr, 
  start_txn_date, 
  main_total_size, 
  tota_size_uom,
  -- 修正后的体积变化百分比计算
  (main_total_size - LAG(main_total_size, 1) OVER (
      PARTITION BY linked_lylty_card_nbr 
      ORDER BY start_txn_date
    )) / NULLIF(LAG(main_total_size, 1) OVER (
      PARTITION BY linked_lylty_card_nbr 
      ORDER BY start_txn_date
    ), 0) AS change_in_volume_per_visit,
  -- 原间隔天数比值计算（已验证正确）
  (start_txn_date - LAG(start_txn_date, 1) OVER (
      PARTITION BY linked_lylty_card_nbr 
      ORDER BY start_txn_date
    ))::FLOAT / main_total_size AS change_in_days_per_visit
FROM 
  Employee
ORDER BY 
  linked_lylty_card_nbr, 
  start_txn_date;

修正后，当体积从500变为1000时，计算结果为(1000-500)/500=1，符合预期的100%变化率。

2. 回访间隔天数比值验证 #

change_in_days_per_visit的计算公式为：

(当前访问日期 - 上一次访问日期的天数差) / 当前体积

验证几个结果行：

• 客户1100000000001959800的第二行：2021-12-21到2022-01-14共24天，24/1000=0.024，与结果一致。
• 客户1100000000001959800的第三行：2022-01-14到2022-01-19共5天，5/500=0.01，与结果一致。
• 客户1100000000006296409的第二行：2021-11-10到2021-11-20共10天，10/500=0.02，与结果一致。

所有验证案例均匹配，说明该列的计算逻辑是正确的。

内容的提问来源于stack exchange，提问作者Mark