求2列和合并行

7qhs6swi  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(398)

主键是 reportdate,market,platform . 如果我得到2个或多个记录,我想求和 impr 以及 leads 列表示row_num=1和delete row_num=2(我计算了delete部分,但仍在尝试sum部分)。
如何求和 impr 以及 leads 列以获得所需的输出,如下所示。
这些是我的输入记录:

reportdate      market  platform    impr            leads   create_date       file_id   row_num
2020-05-16      TX      quote       0.00000         7.00000     2020-06-11      11345   1
2020-05-16      TX      quote       600.00000       0.00000     2020-06-11      11345   2
2020-05-16      CA      street      50.00000        0.00000     2020-06-11      11345   1
2020-05-16      CA      street       0.00000        4.00000     2020-06-11      11345   2
2020-05-16      PA      unknown     0.00000         7.00000     2020-06-11      11345   1
2020-05-16      PA      unknown     600.00000       0.00000     2020-06-11      99999   2

我想将记录作为我的输出:

reportdate      market  platform    impr            leads   create_date       file_id   row_num
2020-05-16      TX      quote       600.00000       7.00000     2020-06-11      11345   1
2020-05-16      CA      street      50.00000        4.00000     2020-06-11      11345   1
2020-05-16      PA      unknown     0.00000         7.00000     2020-06-11      11345   1
2020-05-16      PA      unknown     600.00000       0.00000     2020-06-11      99999   2
qyswt5oh

qyswt5oh1#

这是聚合或窗口函数。
对于当前的数据集,聚合已经足够好了:

select
    reportdate,
    market, 
    platform,
    sum(impr) impr,
    sum(leads) leads,
    create_date,
    file_id,
    min(row_num) row_num
from mytable
group by
    reportdate,
    market, 
    platform,
    create_date,
    file_id

另一方面,如果列中的值不同,例如 file_id 或者 create_date 对于给定的 (reportdate, market, platform) tuple,则需要使用窗口函数:

select *
from (
    select 
        reportdate,
        market, 
        platform,
        sum(impr) over(partition by reportdate, market, platform) impr,
        sum(leads) over(partition by reportdate, market, platform) leads,
        create_date,
        file_id,
        row_num,
        row_number() over(partition by reportdate, market, platform order by row_num) rn
    from mytable
) t
where rn = 1

相关问题