我有一個表格，它向我顯示了一個 user_id 和他們活躍的日期（這是從一個巨大的事件表中得出的）。該表如下所示：
| 用戶身份 | 活動日期 |
|---|---|
| 1 | 2022-06-16 |
| 2 | 2022-06-02 |
| 1 | 2022-06-14 |
| 1 | 2022-05-01 |
我需要創建一個查詢來查找用戶在過去 7 天、8-14 天前、15-21 天前和 22-28 天前是否活躍，以及他們的第一個和最后一個活躍日期。如果可能，我希望將過去 7 天列（以及該型別的其他列）中的活動視為布林值。
第一個和最后一個活動日期非常簡單，但我需要一些其他日期的幫助。這是我到目前為止所得到的：
SELECT
user_id,
MIN(active_date) as first_action_date,
MAX(active_date) as last_action_date,
FROM activity_dates
GROUP BY 1
uj5u.com熱心網友回復：
一種使用方法DATE_DIFF：
WITH activity_dates AS (
SELECT "1" as user_id, DATE("2022-06-16") as active_date
UNION ALL
SELECT "2" as user_id, "2022-06-02" as active_date
UNION ALL
SELECT "1" as user_id, "2022-06-14" as active_date
UNION ALL
SELECT "1" as user_id, "2022-05-01" as active_date
)
SELECT
user_id,
MIN(active_date) as first_action_date,
MAX(active_date) as last_action_date,
MAX(IF(DATE_DIFF(CURRENT_DATE(), active_date, DAY) <= 7, active_date, NULL)) IS NOT NULL as active_last_7_days,
MAX(IF(DATE_DIFF(CURRENT_DATE(), active_date, DAY) BETWEEN 8 AND 14, active_date, NULL)) IS NOT NULL as active_8_14_ago,
MAX(IF(DATE_DIFF(CURRENT_DATE(), active_date, DAY) BETWEEN 15 AND 21, active_date, NULL)) IS NOT NULL as active_15_21_ago,
FROM activity_dates
GROUP BY user_id
輸出：
user_id first_action_date last_action_date active_last_7_days active_8_14_ago active_15_21_ago
1 2022-05-01 2022-06-16 true false false
2 2022-06-02 2022-06-02 false true false
uj5u.com熱心網友回復：
為了達到日期范圍，您可以使用如下的 case 陳述句。識別最近的登錄，然后用它與今天的日期減去 7 天、減去 14 天等進行比較，并根據是否符合條件回傳 true 或 false。
SELECT user_id
, CASE
WHEN lastActive > DATE_ADD(NOW(), INTERVAL -7 DAY) THEN 'TRUE'
ELSE 'FALSE'
END AS "oneWeek"
, CASE
WHEN lastActive > DATE_ADD(NOW(), INTERVAL -14 DAY) THEN 'TRUE'
ELSE 'FALSE'
END AS "twoWeeks"
FROM (SELECT user_id
, MAX(active_date) AS "lastActive"
FROM activity_dates
GROUP BY id) AS table1;
這是它的一個實際操作：
您還可以考慮另一個選項，該選項可為您提供用戶在范圍內活躍的時間
select * from (
select user_id,
min(active_date) over(partition by user_id) first_action_date,
max(active_date) over(partition by user_id) last_action_date,
bucket
from activity_dates, (select [7, 14, 21] days),
unnest([struct(days[safe_offset(range_bucket(date_diff(current_date, active_date, day) - 1, days))] as bucket)])
)
pivot (count(*) active_last_days for bucket in (7, 14, 21))
帶輸出
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/492354.html