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

我正在做一些队列分析，想看看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;

但每周的间隔太少，每月的间隔太多。所以我想要两周。以前有人做过吗？从谷歌上看这是一个挑战
提前谢谢

最佳答案

刚刚解决了，这就是你要做的：

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。四舍五入以避免两周内出现小数

关于postgresql - 从Postgres中的时间戳获取两周，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47672733/