postgresql - 从 Postgres 中的时间戳获取两周

Question

我正在进行一些同类群组分析，想了解 11 月的一组客户中有多少人每周、每两周和每月进行一次交易；以及多长时间

我有一周和一个月的这个(每周示例):

WITH weekly_users AS (
SELECT user_fk
     , DATE_TRUNC('week',created_at) AS week
     , (DATE_PART('year', created_at) - 2016) * 52 + DATE_PART('week', created_at) - 45 AS weeks_between
  FROM transactions
    WHERE created_at >= '2016-11-01' AND created_at < '2017-12-01'
      GROUP BY user_fk, week, weeks_between
),

t2 AS (
  SELECT weekly_users.*
     , COUNT(*) OVER (PARTITION BY user_fk
                          ORDER BY week ROWS BETWEEN UNBOUNDED PRECEDING
                                                  AND 1 PRECEDING) AS prev_rec_cnt
  FROM weekly_users
)
SELECT week
     , COUNT(*)
  FROM t2
 WHERE weeks_between = prev_rec_cnt
 GROUP BY week
 ORDER BY week;

但是每周间隔太短，而每月间隔太多。所以我想要两周。有没有人这样做过？从谷歌搜索这似乎是一个挑战

提前致谢

Answer 1

刚刚解决了，这就是你的做法:

WITH fortnightly_users AS (
    SELECT user_fk
         , EXTRACT(YEAR FROM created_at) * 100 + CEIL(EXTRACT(WEEK FROM created_at)/2) AS fortnight
         , (EXTRACT(YEAR FROM created_at) - 2016) * 26 + CEIL(EXTRACT(WEEK FROM created_at)/2) - 23 AS fortnights_between
      FROM transactions
        WHERE created_at >= '2016-11-01' AND created_at < '2017-12-01'
          GROUP BY user_fk, fortnight, fortnights_between
    ),

    t2 AS (
      SELECT fortnightly_users.*
         , COUNT(*) OVER (PARTITION BY user_fk
                              ORDER BY fortnight ROWS BETWEEN UNBOUNDED PRECEDING
                                                      AND 1 PRECEDING) AS prev_rec_cnt
      FROM fortnightly_users
    )
    SELECT fortnight
         , COUNT(*)
      FROM t2
     WHERE fortnights_between = prev_rec_cnt
     GROUP BY fortnight
     ORDER BY fortnight;

所以你得到周数，然后除以 2。四舍五入以避免两周出现小数