sql - postgresql 查询以生成具有多列的报告
问题描述
我在 postgresql db 中有一个客户事务表,其中包含以下列
transactionId (primary)| customerId(int8)| transactionDate (timestamp)
1 2 2020-02-14
2 3 2020-01-08
3 1 2020-02-06
4 2 2020-02-13
5 2 2020-03-24
需要建立一个查询来创建下面的报告
CustomerId| FirstTransaction| TotalTransactions| Transactions/Week| RecentTransactions
1 2020-02-06 1 1 2020-02-06
3 2020-01-08 1 1 2020-01-08
2 2020-02-13 3 2 2020-03-24
客户第一次开始的时间,总交易量,每周频率,最近一次?并且报告应仅考虑(包含)最近 3 个月的记录。
解决方案
试试下面的,这里是演示。
with cte as
(
select
*,
count(*) over (partition by customerId) as totalTransactions,
1 + floor((extract(day from transactionDate) - 1) / 7) as transactionsWeek
from myTable
where transactionDate >= '2020-01-01'
and transactionDate <= '2020-03-31'
)
select
customerId,
min(transactionDate) as firstTransaction,
max(totalTransactions) as totalTransactions,
max(transactionDate) as recentTransactions,
(ceil(avg(totalTransactions)/count(distinct transactionsWeek))::int) as "Transactions/Week"
from cte
group by
customerId
order by
customerId
输出:
| customerid | firsttransaction | totaltransactions | recenttransactions | Transactions/Week |
| ---------- | ------------------------ | ----------------- | ------------------------ | ----------------- |
| 1 | 2020-02-06 | 1 | 2020-02-06 | 1 |
| 2 | 2020-02-13 | 3 | 2020-03-24 | 2 |
| 3 | 2020-01-08 | 1 | 2020-01-08 | 1 |
在过去三个月中,您还可以使用以下where
条件
transactionDate > CURRENT_DATE - INTERVAL '3 months'
推荐阅读
- python - 在python中随机化快速排序,递归问题
- android - google/android/gms/maps/model/LatLng 的 NoSuchMethodError
- report - 使用 Oracle APEX 5 编辑交互式报表中的行
- apache-spark - 运行结构化流式传输示例“WARN NetworkClient: Bootstrap broker ip-10-28-3-35.ec2.internal:2181 disconnected”时出错
- java - sqlite中的复合索引
- selenium - 使用 Robot Framework 的 Selenium2Library 测试电子应用程序
- c# - stimulsoft 报告是否适用于 exe 文件?
- css - 在运行时为 Angular 应用程序设计样式
- python-3.x - 名称分数项目 euler #22
- php - preg_grep():编译失败:在偏移量 0 处没有可重复的内容