我想每月為每個 id 創建一行，直到end_date.
例如，第一個客戶id在 10 月開始并在 11 月結束。所以我想為客戶活躍的每個月獲取兩行。除此之外，我想創建一個列來標記它是否在該月處于活動狀態。
| id | start_date | end_date |
|----|------------|------------|
| a | 2021-10-02 | 2021-11-15 |
| b | 2021-11-13 | 2021-11-30 |
| c | 2021-11-16 | |
如果沒有end_date，表示它仍然處于活動狀態，則必須持續到當前月份。
示例資料：
WITH t1 AS (
SELECT 'a' AS id, '2021-10-02'::date AS start_date, '2021-11-15'::date AS end_date UNION ALL
SELECT 'b' AS id, '2021-11-13'::date AS start_date, '2021-11-30'::date AS end_date UNION ALL
SELECT 'c' AS id, '2021-11-16'::date AS start_date, NULL::date AS end_date
)
預期結果：
| id | start_date | end_date | months | is_active |
|----|------------|------------|------------|-----------|
| a | 2021-10-02 | 2021-11-15 | 2021-10-01 | TRUE |
| a | 2021-10-02 | 2021-11-15 | 2021-11-01 | FALSE |
| b | 2021-11-13 | 2021-11-30 | 2021-11-01 | FALSE |
| c | 2021-11-16 | | 2021-11-01 | TRUE |
| c | 2021-11-16 | | 2021-12-01 | TRUE |
| c | 2021-11-16 | | 2022-01-01 | TRUE |
我怎樣才能在雪花中實作這一點？
uj5u.com熱心網友回復：
所以如果你有一個范圍，你將需要一些跨越時間的東西來加入，這是可以使用生成器的地方，我將把它放入 CTE 中。我還將使用 ROW_NUMBER() 生成月份步驟序列，以確保沒有間隙。200需要進行硬編碼，因此請輸入一個涵蓋足夠資料以滿足您需要的值，或者將其彈出到表中。
WITH months AS (
SELECT
ROW_NUMBER() OVER (ORDER BY NULL) - 1 as rn
FROM TABLE(generator(rowcount => 200))
)
接下來我們要截斷start_date并找到 end_date 是多少個月后，并將其加入我們的范圍
), range_prep AS (
SELECT id,
start_date,
end_date,
date_trunc(month, start_date) as start_month,
datediff(month, start_month, coalesce(end_date, CURRENT_DATE())) as month_count
FROM data
)
將它們連接在一起，并做：
SELECT id,
r.start_date,
r.end_date,
dateadd(month, m.rn, r.start_month) as months,
(r.end_date is null) OR (date_trunc(month, r.end_date) > months) AS is_active
FROM range_prep as r
JOIN months as m
ON m.rn <= r.month_count
ORDER BY 1,2;
將所有內容與 CTE 放在一起，因為data我們有：
WITH data AS (
SELECT id,
to_date(start_date) as start_date,
to_date(end_date) as end_date
FROM VALUES
('a','2021-10-02','2021-11-15'),
('b','2021-11-13','2021-11-30'),
('c','2021-11-16',null)
v( id, start_date, end_date)
), months AS (
SELECT
ROW_NUMBER() OVER (ORDER BY NULL) - 1 as rn
FROM TABLE(generator(rowcount => 200))
), range_prep AS (
SELECT id,
start_date,
end_date,
date_trunc(month, start_date) as start_month,
datediff(month, start_month, coalesce(end_date, CURRENT_DATE())) as month_count
FROM data
)
SELECT id,
r.start_date,
r.end_date,
dateadd(month, m.rn, r.start_month) as months,
(r.end_date is null) OR (date_trunc(month, r.end_date) > months) AS is_active
FROM range_prep as r
JOIN months as m
ON m.rn <= r.month_count
ORDER BY 1,2;
給出：
| ID | 開始日期 | 結束日期 | 幾個月 | 活躍 |
|---|---|---|---|---|
| 一個 | 2021-10-02 | 2021-11-15 | 2021-10-01 | 真的 |
| 一個 | 2021-10-02 | 2021-11-15 | 2021-11-01 | 錯誤的 |
| b | 2021-11-13 | 2021-11-30 | 2021-11-01 | 錯誤的 |
| C | 2021-11-16 | 2021-11-01 | 真的 | |
| C | 2021-11-16 | 2021-12-01 | 真的 | |
| C | 2021-11-16 | 2022-01-01 | 真的 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/413592.html
標籤：
上一篇：如何使用生成系列連接兩個表