python - 当标志为 True 时重置 SQL 窗口的长度？

Question

我有一个公用事业账单分段表，其中每个分段表示一个月的千瓦时使用情况。我想将 12 人一组合计起来以获得年度账单。

变量bill_cd表示年度账单的最后一部分。 Data download

    cust_id     kwh    bill_cd
0   3333    1104.388683 ?
1   3333    1498.007305 ?
2   3333    662.044822  ?
3   3333    661.342412  ?
4   3333    494.070683  ?
5   3333    300.147843  ?
6   3333    836.677007  ?
7   3333    864.608037  ?
8   3333    933.232845  ?
9   3333    1191.025358 ?
10  3333    1507.119588 ?
11  3333    1980.653631 BILL
12  3333    2621.387010 ?
13  3333    2552.053789 BILL

问题:可以看出，每个年度账单没有 12 个部分。在此代码段中，我只想获取第一个年度账单的总和，因为它将包含 12 个部分，但不想获取第二个年度账单的总和，因为它只有两个部分。

我用 python 编写了这个查询:

import pandas as pd, pandasql as ps

df = pd.read_csv('so_ex.csv')

q1 = """
select cust_id, kwh, bill_cd,
sum(kwh) over (partition by cust_id
    rows between 11 preceding and current row) as kwh_total,
count(kwh) over (partition by cust_id
    rows between 11 preceding and current row) as bseg_count
from df
"""

ps.sqldf(q1, locals())

结果如下。有没有办法在传递“BILL”实例后重置计数变量，以便帐单段计数变为 1。这样，我可以稍后在 bseg_count=12 处过滤表。

   cust_id      kwh   bill_cd  kwh_total bseg_count
0   3333    1104.388683 ?   1104.388683   1
1   3333    1498.007305 ?   2602.395988   2
2   3333    662.044822  ?   3264.440810   3
3   3333    661.342412  ?   3925.783223   4
4   3333    494.070683  ?   4419.853906   5
5   3333    300.147843  ?   4720.001748   6
6   3333    836.677007  ?   5556.678755   7
7   3333    864.608037  ?   6421.286792   8
8   3333    933.232845  ?   7354.519638   9
9   3333    1191.025358 ?   8545.544996   10
10  3333    1507.119588 ?   10052.664584  11
11  3333    1980.653631 BILL 12033.318215 12
12  3333    2621.387010 ?    13550.316542 12
13  3333    2552.053789 BILL 14604.363026 12

期望的输出:

   cust_id      kwh   bill_cd  kwh_total bseg_count
0   3333    1104.388683 ?   1104.388683   1
1   3333    1498.007305 ?   2602.395988   2
2   3333    662.044822  ?   3264.440810   3
3   3333    661.342412  ?   3925.783223   4
4   3333    494.070683  ?   4419.853906   5
5   3333    300.147843  ?   4720.001748   6
6   3333    836.677007  ?   5556.678755   7
7   3333    864.608037  ?   6421.286792   8
8   3333    933.232845  ?   7354.519638   9
9   3333    1191.025358 ?   8545.544996   10
10  3333    1507.119588 ?   10052.664584  11
11  3333    1980.653631 BILL 12033.318215 12
12  3333    2621.387010 ?    13550.316542 1
13  3333    2552.053789 BILL 14604.363026 2

Answer 1

您可以反向执行 BILL 的累积计数来定义组，然后使用 row_number():

select df.*,
       row_number() over (partition by cust_id, grp order by <ordering col> desc)
from (select df.*,
             sum(case when bill_cd = 'BILL' then 1 else 0 end) over (partition by cust_id order by <ordering col>) as grp
      from df
     ) df

这些窗口函数应基于排序列(例如日期或顺序 ID)。目前尚不清楚该列是什么。