我需要查询以显示new_customers X customer_cancellations
通过此查询,我可以按月获取new_customers:
从客户那里选择count(start_date),to_char(start_date,'MM')作为monthNumber,to_char(start_date,'YY')作为yearNumber ,其中start_date不为null的客户 按to_char(start_date,'MM'),to_char(start_date,' YY') 按yearNumber,monthNumber排序;
通过其他方式,我可以按月获得客户取消订单:
从客户中选择count(cancellation_date),to_char(cancellation_date,'MM')作为monthNumber,to_char(cancellation_date,'YY')作为yearNumber ,其中cancel_date不为null的客户 按to_char(cancellation_date,'MM'),to_char(cancellation_date,' YY') 按yearNumber,monthNumber排序;
这两个查询都返回类似:
计数| monthnumber | yearnumber 1 | 1 | 20 7 | 2 | 20 5 | 3 | 20
但我想要这样的事情:
customer_out_count | customer_in_count | monthnumber | yearnumber 0 | 1 | 1 | 20 0 | 7 | 2 | 20 1 | 0 | 3 | 20 0 | 1 | 4 | 20 5 | 7 | 5 | 20 1 | 5 | 6 | 20
我已经尝试过这个其他查询:
从客户那里选择“开始”作为类型,将count(开始日期)作为计数,将to_char(开始日期,“ MM”)作为monthNumber,将to_char(开始日期,“ YY”)作为yearNumber,从开始日期不为null的客户开始 按to_char(start_date,'MM '),to_char(开始日期,'YY') 联合 选择'cancellation'作为类型,count(cancellation_date)作为counta,to_char(cancellation_date,'MM')作为monthNumber,to_char(cancellation_date,'YY')作为yearNumber 从客户那里 cancel_date不是空值, 按to_char(cancellation_date,'MM'),to_char(cancellation_date,'YY')按yearNumber,monthNumber顺序分组;
结果是“确定”:
类型| newcount | monthnumber | yearnumber 开始| 1 | 1 | 20 取消| 1 | 1 | 20 取消| 7 | 2 | 20 开始| 3 | 3 | 20 取消| 1 | 4 | 20 开始| 7 | 5 | 20 开始| 5 | 6 | 20
但是我将需要对代码进行一些操作才能实现所需的功能。
如何将这两个查询合并为一个?我正在使用postgreslq。
一个选项取消对行的透视,然后聚合。在Postgres中,您可以通过横向联接来表达这一点:
select
sum(x.customer_in_count) as customer_in_count,
sum(x.customer_out_count) as customer_out_count
to_char(x.dt, 'MM') as monthNumber,
to_char(x.dt, 'YY') as yearNumber
from customer c
cross join lateral (values
(c.start_date, 1, 0), (c.cancellation_date, 0, 1)
) as x(dt, customer_in_count, customer_out_count)
where x.dt is not null
group by monthNumber, yearNumber
order by monthNumber, yearNumber
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句