有交易流水表bank_records表,包含两个字段:KH(银行卡号),JYSJ(交易时间) 1. 计算2023年每个月,每个银行卡号连续交易的最多天数 2. 计算2023年每个月,连续2天都有交易的银行卡号 3. 计算2023年每个月,连续5天都有交易的银行卡数量
时间: 2024-05-04 13:18:37 浏览: 60
1. 计算2023年每个月,每个银行卡号连续交易的最多天数:
```sql
SELECT
KH,
DATE_TRUNC('month', JYSJ) AS month,
MAX(DATE_PART('day', DATES.d2 - DATES.d1)) AS max_consecutive_days
FROM (
SELECT
KH,
JYSJ,
ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) AS rn,
JYSJ - ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) * INTERVAL '1 day' AS grp
FROM bank_records
WHERE EXTRACT(year FROM JYSJ) = 2023
) AS t
JOIN (
SELECT generate_series(MIN(JYSJ), MAX(JYSJ), INTERVAL '1 day') AS d1,
generate_series(MIN(JYSJ), MAX(JYSJ), INTERVAL '1 day') + INTERVAL '1 day' AS d2
FROM bank_records
WHERE EXTRACT(year FROM JYSJ) = 2023
) AS DATES
ON t.JYSJ BETWEEN DATES.d1 AND DATES.d2
GROUP BY KH, month
ORDER BY KH, month
```
2. 计算2023年每个月,连续2天都有交易的银行卡号:
```sql
SELECT
KH,
DATE_TRUNC('month', JYSJ) AS month
FROM (
SELECT
KH,
JYSJ,
ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) AS rn,
JYSJ - ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) * INTERVAL '1 day' AS grp
FROM bank_records
WHERE EXTRACT(year FROM JYSJ) = 2023
) AS t
WHERE EXISTS (
SELECT 1
FROM (
SELECT
KH,
JYSJ,
ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) AS rn,
JYSJ - ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) * INTERVAL '1 day' AS grp
FROM bank_records
WHERE EXTRACT(year FROM JYSJ) = 2023
) AS t2
WHERE t.KH = t2.KH AND t.rn = t2.rn + 1 AND t.grp = t2.grp
)
GROUP BY KH, month
ORDER BY KH, month
```
3. 计算2023年每个月,连续5天都有交易的银行卡数量:
```sql
SELECT
DATE_TRUNC('month', JYSJ) AS month,
COUNT(DISTINCT KH) AS num_of_cards
FROM (
SELECT
KH,
JYSJ,
ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) AS rn,
JYSJ - ROW_NUMBER() OVER (PARTITION BY KH, DATE_TRUNC('month', JYSJ) ORDER BY JYSJ) * INTERVAL '1 day' AS grp
FROM bank_records
WHERE EXTRACT(year FROM JYSJ) = 2023
) AS t
JOIN (
SELECT generate_series(MIN(JYSJ), MAX(JYSJ), INTERVAL '1 day') AS d1,
generate_series(MIN(JYSJ), MAX(JYSJ), INTERVAL '1 day') + INTERVAL '4 day' AS d2
FROM bank_records
WHERE EXTRACT(year FROM JYSJ) = 2023
) AS DATES
ON t.JYSJ BETWEEN DATES.d1 AND DATES.d2 AND t.grp = DATES.d1 - ROW_NUMBER() OVER (PARTITION BY t.KH, DATE_TRUNC('month', t.JYSJ) ORDER BY t.JYSJ) * INTERVAL '1 day'
GROUP BY month
ORDER BY month
```