postgresql中垂直表的sql类查询透视表

g6baxovj  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(304)

我想从垂直列表中编写一个类似pivot表的sql查询。我们来看一个例子:
我的表数据:
表1->元数据:

id text  
1  t1
2  t2
3  t3

表2->报告:

date          category      revenue  metadata_id
2020-01-01    TRIAL           1          1
2020-01-01    PURCHASE       1.2         2
2020-01-03    SUBSCRIPTION   1.4         3
2020-01-03    PURCHASE       1.1         3

...
在这里,我想创建一个sql查询,从特定的范围和ID过滤器中获取结果,如:

Request:
start-date: 2020-01-01
end-date: 2020-01-30
ids: 1,2....100

预期结果:

id       text       category_trial      category_purchase   category_SUBSCRIPTION
1         t1             1                      0                     0
2         t2             0                      1.2                   0
3         t3             0                      1.1                  1.4

在这里,我编写了如下sql:

select
m.id,
m.text,
t1.rev as category_trial,
t2.rev as category_purchase,
t3.rev as category_SUBSCRIPTION
from metadata m
left join 
(
  select 
metadata_id,
sum(revenue) as rev
from report where category = 'TRIAL' and report_date between '2020-01-01' and '2020-01-30'
group by  metadata_id
) t1 on t1.metadata_id = m.id 
left join 
(
  select 
metadata_id,
sum(revenue) as rev
from report where category = 'PURCHASE' and report_date between '2020-01-01' and '2020-01-30'
group by  metadata_id
) t2 on t2.metadata_id = m.id 
left join 
(
  select 
metadata_id,
sum(revenue) as rev
from report where category = 'SUBSCRIPTION' and report_date between '2020-01-01' and '2020-01-30'
group by  metadata_id
) t3 on t3.metadata_id = m.id 
...

在这里我有7个以上的类别。
我的问题是,这个sql可以工作,但是性能还不够。有什么建议可以改进它的性能吗?
注意:我是用postgresql写的,我使用索引。

qnzebej0

qnzebej01#

我不经常使用postgres,但是这个查询应该更快:

select id, text,
       sum(case category when 'TRIAL'        then revenue else 0 end) cat_tri,
       sum(case category when 'PURCHASE'     then revenue else 0 end) cat_pur,
       sum(case category when 'SUBSCRIPTION' then revenue else 0 end) cat_sub   
  from (
    select id, text, category, revenue 
      from metadata m join report r on m.id = r.metadata_id
      where date_ between '2020-01-01' and '2020-01-30' 
        and id between 1 and 100 ) t   
  group by id, text 
  order by id

D小提琴
结果与预期一样,只有一个联接、筛选和分组。

相关问题