leetcode 1336 每次访问的交易次数(postgresql)

需求

表: Visits

±--------------±--------+
| Column Name | Type |
±--------------±--------+
| user_id | int |
| visit_date | date |
±--------------±--------+
(user_id, visit_date) 是该表的主键
该表的每行表示 user_id 在 visit_date 访问了银行

表: Transactions

±-----------------±--------+
| Column Name | Type |
±-----------------±--------+
| user_id | int |
| transaction_date | date |
| amount | int |
±-----------------±--------+
该表没有主键，所以可能有重复行
该表的每一行表示 user_id 在 transaction_date 完成了一笔 amount 数额的交易
可以保证用户 (user) 在 transaction_date 访问了银行 (也就是说 Visits 表包含 (user_id, transaction_date) 行)

银行想要得到银行客户在一次访问时的交易次数和相应的在一次访问时该交易次数的客户数量的图表

写一条 SQL 查询多少客户访问了银行但没有进行任何交易，多少客户访问了银行进行了一次交易等等

结果包含两列：

transactions_count： 客户在一次访问中的交易次数
visits_count： 在 transactions_count 交易次数下相应的一次访问时的客户数量
transactions_count 的值从 0 到所有用户一次访问中的 max(transactions_count)

按 transactions_count 排序

下面是查询结果格式的例子：

Visits 表:
±--------±-----------+
| user_id | visit_date |
±--------±-----------+
| 1 | 2020-01-01 |
| 2 | 2020-01-02 |
| 12 | 2020-01-01 |
| 19 | 2020-01-03 |
| 1 | 2020-01-02 |
| 2 | 2020-01-03 |
| 1 | 2020-01-04 |
| 7 | 2020-01-11 |
| 9 | 2020-01-25 |
| 8 | 2020-01-28 |
±--------±-----------+
Transactions 表:
±--------±-----------------±-------+
| user_id | transaction_date | amount |
±--------±-----------------±-------+
| 1 | 2020-01-02 | 120 |
| 2 | 2020-01-03 | 22 |
| 7 | 2020-01-11 | 232 |
| 1 | 2020-01-04 | 7 |
| 9 | 2020-01-25 | 33 |
| 9 | 2020-01-25 | 66 |
| 8 | 2020-01-28 | 1 |
| 9 | 2020-01-25 | 99 |
±--------±-----------------±-------+
结果表:
±-------------------±-------------+
| transactions_count | visits_count |
±-------------------±-------------+
| 0 | 4 |
| 1 | 5 |
| 2 | 0 |
| 3 | 1 |
±-------------------±-------------+

• 对于 transactions_count = 0, visits 中 (1, “2020-01-01”), (2, “2020-01-02”), (12, “2020-01-01”) 和 (19, “2020-01-03”) 没有进行交易，所以 visits_count = 4 。
• 对于 transactions_count = 1, visits 中 (2, “2020-01-03”), (7, “2020-01-11”), (8, “2020-01-28”), (1, “2020-01-02”) 和 (1, “2020-01-04”) 进行了一次交易，所以 visits_count = 5 。
• 对于 transactions_count = 2, 没有客户访问银行进行了两次交易，所以 visits_count = 0 。
• 对于 transactions_count = 3, visits 中 (9, “2020-01-25”) 进行了三次交易，所以 visits_count = 1 。
• 对于 transactions_count >= 4, 没有客户访问银行进行了超过3次交易，所以我们停止在 transactions_count = 3 。

输入

分析

1 两表关联,查询出transaction_date也就是交易时间非空的数据,跟距transaction_date分组,对每组数据求和
2 对上一步求和的结果再次分组,求和,求出不同交易次数对应的日期有多少个
3 求出transaction_date为空的数据,也即是交易次数为0的日期数
4 上面的两个结果进行汇总
5 根据交易次数的最大值,生产一个从0到最大值的整数序列,将其放在一张临时表中
6 临时表和汇总表关联,求出所有交易次数对应的交易日期数

输出

with t1 as (-- 两表关联,查询出transaction_date也就是交易时间非空的数据,跟距transaction_date分组,对每组数据求和
    select transaction_date, count(1) as cnt
    from visits v
             left join transactions t
                       on v.user_id = t.user_id and v.visit_date = t.transaction_date
    where transaction_date notnull
    group by transaction_date),
     t2 as (-- 对上一步求和的结果再次分组,求和,求出不同交易次数对应的日期有多少个
         select cnt, count(1) as visits_count
         from t1
         group by cnt),
     t3 as (-- 求出transaction_date为空的数据,也即是交易次数为0的日期数
         select case when transaction_date is null then 0 end as cnt, count(1) as visits_count
         from visits v
                  left join transactions t
                            on v.user_id = t.user_id and v.visit_date = t.transaction_date
         where transaction_date is null
         group by transaction_date),
     t4 as (-- 上面的两个结果进行汇总
         select *
         from t2
         union all
         select *
         from t3),
     t5 as (-- 根据交易次数的最大值,生产一个从0到最大值的整数序列,将其放在一张临时表中
         SELECT generate_series(0, max(cnt)) AS sequence
         from t4)
-- 临时表和汇总表关联,求出所有交易次数对应的交易日期数
select sequence as transaction_count, coalesce(visits_count, 0) as visits_count
from t5
         left join t4 on t5.sequence = t4.cnt
order by transaction_count
;