<think>我们有一个表question5_1，包含两列：客户（customer）和交易日期（trade_date）。
目标：转换为每个日期（trade_date）、每个客户（customer），以及一个标识（是否连续三个月交易）的数据形式。
注意："是否连续三个月交易"的意思是：对于当前行（某个客户在某一天交易），判断该客户是否在接下来的三个月内每个月都有交易（即连续三个月都有交易记录）。
这里需要注意：连续三个月指的是自然月，比如2023-01-15，那么连续三个月就是2023-02、2023-03、2023-04。但是交易日期可能不是连续的，我们只需要每个月至少有一次交易就算。
思路：
1. 首先，我们需要为每个客户生成每个月的记录（因为最终输出是按月的，而不是按天）。但我们原始数据是按天的，所以第一步需要将交易日期转换为月份（截断到月），并去重（每个客户每个月只保留一条记录，因为我们关心的是每个月是否有交易）。
2. 然后，使用开窗函数，按客户分组，按月份排序，然后计算接下来的两个月的月份（通过偏移）是否有记录。
但是，问题要求输出的是“日期”（trade_date）？这里注意：原始表是按天的，但转换后我们按月份来考虑？还是按天？根据问题描述，目标形式是“日期、客户、是否连续三个月交易”。这里日期是指原始交易日期吗？但是连续三个月是一个时间段，所以对于同一个客户，在连续三个月的每个月中，每一天都会标记为“是”吗？这似乎不太合理。
重新理解：对于原始表中的每一行（一个客户在某一天有一条交易记录），我们需要判断该客户从这一天开始，接下来的三个月（自然月）中，每个月是否都有交易（即每月至少一次交易）。注意，这里是以每一天为基准，判断从该天所在月份开始的连续三个月。
例如：客户A在2023-01-15交易了一次，那么我们要看客户A在2023-02、2023-03、2023-04这三个月是否都有交易（至少一次）。注意：2023-01-15所在的月份是2023年1月，那么连续三个月应该是2023-01、2023-02、2023-03？还是从下一个月份开始？
题目要求“连续三个月交易”，通常是指包含当前月以及接下来的两个月？还是从当前月开始的连续三个月？还是从当前月的下一个月开始连续三个月？这里需要明确。
根据常见的业务场景（比如判断连续三个月活跃），通常是指当前月以及接下来的两个月（共三个月）。所以：
当前月：trade_date所在的月份（记为month0）
下个月：month0+1（记为month1）
下下个月：month0+2（记为month2）
那么，对于每一行，我们要判断该客户在month0、month1、month2这三个月是否都有交易记录。
然而，注意：当前行已经证明了month0有交易，所以我们只需要判断month1和month2是否有交易。
但是，问题要求输出的是原始的行（按天），所以每条记录都要判断。
步骤：
1. 首先，我们为每个客户计算每个交易日期对应的月份（trade_month），然后我们还需要知道该客户有交易的月份集合（以便判断后续月份是否存在）。
2. 然后，我们可以在原始表（按天）的基础上，为每一行（每个客户，每个交易日期）计算：
是否连续三个月交易 = 该客户在trade_date所在月份的下个月和下下个月都有交易记录吗？
但是，注意：同一个客户在同一个月份可能有多个交易日期，我们只需要判断该月份是否有交易，不需要多次判断。
因此，我们可以这样做：
a. 先构建一个辅助子查询，得到每个客户每个有交易的月份（去重）。
b. 然后，在原始表的基础上，使用EXISTS或开窗函数来检查后续两个月份是否存在。
具体步骤：
方法1（使用子查询）：
对于原始表t1的每一行，我们检查：
是否存在下个月的记录？即：存在一条记录（同一个客户）的交易日期所在的月份 = 当前月份+1
是否存在下下个月的记录？即：存在一条记录（同一个客户）的交易日期所在的月份 = 当前月份+2
但是，这样效率可能不高，而且需要多次关联。
方法2（使用开窗函数）：
我们可以先构建每个客户每个月的交易记录（一行代表一个月），然后在这个按月的结果集上，使用LEAD函数来获取接下来的两个月份是否存在。
然后，再把这个按月的结果关联回原来的按天记录？但是按月的结果只有一个月一条，而按天的记录在一个月内有多条。
所以，我们可以：
步骤1：将原始表按客户和月份分组（得到客户每个月的交易月份），并标记该月份（比如用1表示有交易，NULL表示没有，但实际上我们只取有交易的月份）。
步骤2：使用LEAD函数，按客户分组，月份排序，获取接下来两个月份（按自然月顺序）的记录。如果接下来第一个月存在（即LEAD的第一行）且月份正好是当前月份+1，并且接下来第二个月份存在且正好是当前月份+2，那么当前月份就满足连续三个月（当前月，下个月，下下月）都有交易。
但是，这样得到的是按月的结果。我们需要将这个结果关联回原始表，因为原始表是按天的。
步骤3：将按月的结果（每个客户，月份，以及一个标记“是否连续三个月交易”）关联到原始表，关联条件为同一个客户，并且原始交易日期所在的月份=月份结果中的月份。
这样，原始表中的每一天都会获得它所在月份是否满足连续三个月交易（注意，同一个月份内的所有天，这个标记是一样的）。
但是，注意：我们判断的是“当前月份”是否满足连续三个月，而连续三个月是指当前月、下个月、下下月。所以，对于原始表中的每一天，我们标记的是：该天所在的月份（设为M）以及它的下个月（M+1）和下下个月（M+2）是否都有交易。
因此，对于某一天，如果它所在的月份M满足（M, M+1, M+2）都有交易，那么这一天的记录就标记为“是”，否则为“否”。
但是，这里有一个问题：如果某客户在1月有多次交易，在2月也有多次交易，在3月也有多次交易，那么该客户在1月的每一天都会被标记为“是”；在2月的每一天也会被标记为“是”（因为2月、3月、4月都要有？不对，2月的连续三个月是2月、3月、4月）？不对，对于2月的记录，它的连续三个月应该是2月、3月、4月。所以每个月的标记都是独立的。
所以，我们需要按月计算连续三个月的标记，然后按月份关联回每一天。
具体SQL如下：
步骤1：从原始表question5_1中提取每个客户每个月份（去重），并转换为月份（trunc(trade_date, 'MM')）
创建临时视图monthly_trades：
SELECT customer, TRUNC(trade_date, 'MM') AS trade_month
FROM question5_1
GROUP BY customer, TRUNC(trade_date, 'MM')
步骤2：使用LEAD函数，按客户分组，按月份排序，获取后面两个月份
创建临时视图连续标记：
SELECT customer, trade_month,
LEAD(trade_month, 1) OVER (PARTITION BY customer ORDER BY trade_month) AS next_month1,
LEAD(trade_month, 2) OVER (PARTITION BY customer ORDER BY trade_month) AS next_month2
FROM monthly_trades
步骤3：然后判断：如果next_month1等于trade_month+1个月（即ADD_MONTHS(trade_month,1)），并且next_month2等于trade_month+2个月（即ADD_MONTHS(trade_month,2)），那么说明连续三个月都有交易。
注意：因为LEAD取的是实际存在的月份，所以我们只需要检查：
next_month1 = ADD_MONTHS(trade_month, 1)
且 next_month2 = ADD_MONTHS(trade_month, 2)
那么，这个trade_month就满足连续三个月交易。
创建视图monthly_flag：
SELECT customer, trade_month,
CASE WHEN (next_month1 = ADD_MONTHS(trade_month, 1)
AND next_month2 = ADD_MONTHS(trade_month, 2))
THEN '是'
ELSE '否'
END AS is_continuous
FROM 连续标记
步骤4：将按月计算的标记关联回原始表（按天）。注意，原始表的一条记录可能有多个月份，所以我们用月份关联。
原始表：question5_1，我们提取交易日期所在的月份：TRUNC(trade_date, 'MM')，然后和monthly_flag进行关联。
最终SQL：
WITH monthly_trades AS (
SELECT DISTINCT customer, TRUNC(trade_date, 'MM') AS trade_month
FROM question5_1
),
lead_months AS (
SELECT customer, trade_month,
LEAD(trade_month, 1) OVER (PARTITION BY customer ORDER BY trade_month) AS next_month1,
LEAD(trade_month, 2) OVER (PARTITION BY customer ORDER BY trade_month) AS next_month2
FROM monthly_trades
),
monthly_flag AS (
SELECT customer, trade_month,
CASE
WHEN next_month1 = ADD_MONTHS(trade_month, 1)
AND next_month2 = ADD_MONTHS(trade_month, 2)
THEN '是'
ELSE '否'
END AS is_continuous
FROM lead_months
)
SELECT
q.trade_date, q.customer,
mf.is_continuous AS "是否连续三个月交易"
FROM question5_1 q
LEFT JOIN monthly_flag mf
ON q.customer = mf.customer
AND TRUNC(q.trade_date, 'MM') = mf.trade_month
注意：这里我们使用了LEFT JOIN，确保原始表的每一行都会出现。如果一个月份在monthly_flag中没有（但实际不会，因为monthly_trades已经包含了所有出现的月份），那么is_continuous就会是NULL，但我们用CASE WHEN已经处理了所有情况，所以不会有NULL？不对，在lead_months中，如果一个月份的最后两个月份不存在（即没有后续两个月），那么LEAD得到的就是NULL，这时CASE WHEN就会返回'否'。
但是，这里有一个边界情况：如果客户在某个月份之后就没有交易了，那么后续两个月就不存在，所以标记为'否'。
另一种思路：我们也可以不构建按月去重的临时表，而是直接在原始表上使用开窗函数，按月份去重后使用LEAD。但是，去重是必要的，因为同一个月份多次出现会导致LEAD得到同一个月份多次。
所以，这个SQL应该能满足要求。
但是，请注意：如果同一个月份有多个交易记录，我们在monthly_trades中只保留一条，然后在关联回按天记录时，同一个月份内的所有天都会得到同一个标记。
这就是我们想要的结果。
因此，最终SQL如下：
</think>要使用 Oracle 的偏移开窗函数实现需求，需按以下步骤操作：
### 解决方案
```sql
WITH MonthlyTrades AS (
SELECT
customer,
TRUNC(trade_date, 'MM') AS month_start, -- 按月分组
MIN(trade_date) AS min_date_in_month -- 取月份内任意日期代表该月
FROM question5_1
GROUP BY customer, TRUNC(trade_date, 'MM')
),
FlaggedMonths AS (
SELECT
customer,
month_start,
-- 检查后续两个月是否存在交易
CASE
WHEN LEAD(month_start, 1) OVER (PARTITION BY customer ORDER BY month_start) = ADD_MONTHS(month_start, 1)
AND LEAD(month_start, 2) OVER (PARTITION BY customer ORDER BY month_start) = ADD_MONTHS(month_start, 2)
THEN '是'
ELSE '否'
END AS is_continuous
FROM MonthlyTrades
)
SELECT
q.trade_date AS "日期",
q.customer AS "客户",
f.is_continuous AS "是否连续三个月交易" -- 包含当前月及后续两月
FROM question5_1 q
JOIN FlaggedMonths f
ON q.customer = f.customer
AND TRUNC(q.trade_date, 'MM') = f.month_start
ORDER BY q.trade_date, q.customer;
```
### 关键说明
1. **逻辑分层处理**：
- **MonthlyTrades**：将原始日粒度数据按月聚合，使用 `TRUNC(trade_date, 'MM')` 截断到月
- **FlaggedMonths**：通过 `LEAD()` 开窗函数检查后续两个月是否存在交易记录
- **最终关联**：将月级标记结果关联回原始日粒度数据
2. **偏移开窗核心**：
```sql
LEAD(month_start, 1) OVER (PARTITION BY customer ORDER BY month_start)
= ADD_MONTHS(month_start, 1)
```
- `LEAD(,1)` 获取下一个月份记录
- `ADD_MONTHS(,1)` 计算理论上的下个月份
- 两者相等说明下月有交易
3. **连续三个月定义**：
- 包含当前月 + 后续两个月（如1月数据需验证2月、3月存在交易）
- 同一客户同月内所有日期共享相同标记
### 示例效果
原始数据：
| 客户 | 交易日期 |
|------|-----------|
| A | 2023-01-05|
| A | 2023-02-10|
| A | 2023-03-15|
| B | 2023-01-20|
输出结果：
| 日期 | 客户 | 是否连续三个月交易 |
|------------|------|-------------------|
| 2023-01-05 | A | 是 | -- 因A在2/3月有交易
| 2023-01-20 | B | 否 | -- B后续月份无交易
| 2023-02-10 | A | 否 | -- A在4月无交易（需验证3/4月）
| 2023-03-15 | A | 否 | -- 后续不足两月
> 注：最终月份是否标记为"是"取决于其自身及后续两个月的交易连续性验证[^1]。
蓝桥杯对局匹配算法解析
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
