我有以下 SQL 表
| 用戶名 | 月 |
|---|---|
| 292 | 10 |
| 123 | 12 |
| 123 | 1 |
| 123 | 2 |
| 123 | 4 |
| 345 | 6 |
| 345 | 7 |
我想查詢它，以獲取每個用戶名在連續月份計數中的登錄記錄。意味著我正在尋找的最終結果如下所示：
| 用戶名 | 條紋 |
|---|---|
| 292 | 1 |
| 123 | 3 |
| 345 | 2 |
我怎樣才能實作它？注意到第 12 個月 --> 第 1 個月的問題；
感謝你的幫助;
uj5u.com熱心網友回復：
這會給你你想要的結果：
select username, count(*)
from (
select
username
, month_1
, coalesce(nullif(lead(month_1)
over (partition by username
order by coalesce(nullif(month_1,12),0))
- coalesce(nullif(month_1,12),0),-1),1) as MonthsTillNext
from login_tab
) Step1
where MonthsTillNext=1
group by username
通過計算與下一行的差異，其中下一行按升序定義為下一個month_no，將12視為0（請參閱我在評論中提到的歧義）。然后它只留下連續幾個月的行，并對它們進行計數。
但請注意，除了月份：12 左右的例外，還有另一種情況沒有考慮：如果用戶的月份是 1、2、3 和 6、7、8，這將被視為 Streak：6；是你想要的嗎？
uj5u.com熱心網友回復：
一種方法是使用遞回 CTE，例如
WITH RECURSIVE cte (username, month, cnt) AS
(
SELECT username, month, 1
FROM test
UNION ALL
SELECT test.username, test.month, cte.cnt 1
FROM cte INNER JOIN test
ON cte.username = test.username AND CASE WHEN cte.month = 12 THEN 1 ELSE cte.month 1 END = test.month
)
SELECT username, MAX(cnt)
FROM cte
GROUP BY username
ORDER BY username
這個想法是 CTE（cte在我的示例中命名）在用戶相同且月份為下一個的條件下遞回地連接回表。所以對于用戶 345，你有：
| 用戶名 | 月 | 計數 |
|---|---|---|
| 345 | 6 | 1 |
| 345 | 7 | 1 |
| 345 | 7 | 2 |
cnt=1 的行來自原始表（額外的cnt列硬編碼為 1），cnt=2 的行來自查詢的遞回部分（找到匹配項并用于cnt 1其 cnt）。然后查詢為每個用戶選擇最大值。
連接使用CASE陳述句來處理 12 后跟 1。
您可以在這個 fiddle 中看到它與您的示例資料一起使用。
uj5u.com熱心網友回復：
@EdmCoff 分享的那一款非常優雅。
另一個沒有遞回并且只使用條件邏輯的 -
with data_cte as
(
select username, month_1,
case when (count(month_1) over (partition by username) = 1) then 1
when (lead(month_1) over (partition by username order by username) - month_1) = 1 OR (month_1 - lag(month_1) over (partition by username order by username)) = 1 then 1
when (month_1 = 12 and min (month_1) over (partition by username) =1) then 1
end cnt
from login_tab
)
select username, count(cnt) from data_cte group by username
DB Fiddle在這里。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/483440.html