如果列 x 上的用户记录不为空，我如何计算该用户第一次不为空后拥有的记录数？

debugcn 发表于 Dev

克里斯

我想在第一次 x 不为该用户为空之后为每个用户创建一个记录数计数。

我有一个类似于以下内容的表：

 id | user_id | completed_at | x
----+---------+--------------+---
  1 |    1001 | 2017-06-01   | 1
 20 |    1001 | 2017-06-01   | 2
 21 |    1001 | 2017-06-02   | 4
 22 |    1001 | 2017-06-03   |
 24 |    1001 | 2017-06-03   |
 25 |    1001 | 2017-06-04   |
 23 |    1001 | 2017-06-04   |
 12 |    1001 | 2017-06-06   |
 13 |    1001 | 2017-06-07   |
 14 |    1001 | 2017-06-08   |
  2 |    1002 | 2017-06-02   | 3
 27 |    1002 | 2017-06-02   | 7
 15 |    1002 | 2017-06-09   |
  3 |    1003 | 2017-06-03   |
  4 |    1004 | 2017-06-04   |
  5 |    1005 | 2017-06-05   |
 33 |    1005 | 2017-06-20   | 8
 34 |    1006 | 2017-07-10   | 9
  6 |    1006 | 2017-10-06   |
  7 |    1007 | 2017-10-07   |
  8 |    1008 | 2017-10-08   |
  9 |    1009 | 2017-10-09   |
 10 |    1010 | 2017-10-10   |
 16 |    1011 | 2017-06-01   |
 11 |    1011 | 2017-07-01   | 5
 17 |    1012 | 2017-06-02   |
 26 |    1012 | 2017-07-02   | 6
 18 |    1013 | 2017-06-03   |
 19 |    1014 | 2017-06-04   |
 31 |    1014 | 2017-06-24   |
 32 |    1014 | 2017-06-24   |
 30 |    1014 | 2017-06-24   |
 29 |    1014 | 2017-06-24   |
 28 |    1014 | 2017-06-24   |

预期的输出如下所示：

+------+------------+---------------+
| user |  first_x   | records_after |
+------+------------+---------------+
| 1001 | 2017-06-01 |             9 |
| 1002 | 2017-06-02 |             2 |
| 1005 | 2017-06-20 |             0 |
| 1011 | 2017-07-01 |             0 |
| 1012 | 2017-07-02 |             0 |
+------+------------+---------------+

亚历山大议员

使用运行计数，然后使用运行计数 > 0 的条件计数

样本

WITH flags AS (
    SELECT
      user_id,
      completed_at,
      sum(CASE WHEN x IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY user_id ORDER BY completed_at ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS flag
    FROM users
),
completed AS (
    SELECT DISTINCT ON (user_id)
      user_id,
      completed_at AS first_x
    FROM flags
    WHERE flag > 0
    ORDER BY user_id, completed_at
)
SELECT DISTINCT
  user_id AS user,
  first_x,
  count(flag) FILTER (WHERE flag>0) - 1 AS records_after
FROM flags
NATURAL JOIN completed
GROUP BY 1, 2
ORDER BY 1

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。