时间戳

首页 > 解决方案 > 标志为真时重置 SQL 窗口的长度？

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

问题描述

我有一个公用事业账单段表，其中每个段都说明了一个月的千瓦时使用量。我想将 12 人一组相加得到年度账单。

变量bill_cd表示年度账单的最后一段。 资料下载

    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

标签： pythonsqlif-statementpartition

解决方案

您可以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）。目前尚不清楚该列是什么。

推荐阅读