sql - 如何返回连续两天或更多天/月/季度符合条件的行？

Question

我试图返回连续两个月或更多个月满足 >=10 FactCount 阈值的行。

这是我当前拥有的输出示例，查询如下。

我将如何实现这一目标？如果有什么我可以澄清的，请发表评论。感谢您的意见!

现有表

CREATE TABLE foo AS SELECT * FROM ( VALUES
( 5454, 201601, 5),
( 5454, 201602, 3),
( 5454, 201603, 11),
( 5454, 201604, 10),
( 5454, 201605, 6),
( 9987, 201601, 2),
( 9987, 201602, 0),
( 9987, 201603, 12),
( 9987, 201604, 5),
( 9987, 201605, 7)
) AS t(userid , yearmonth , factcount);

期望的输出

+--------+-----------+-----------+
| UserID | YearMonth | FactCount |
+--------+-----------+-----------+
| 5454   | 201601    | 5         |
+--------+-----------+-----------+
| 5454   | 201602    | 3         |
+--------+-----------+-----------+
| 5454   | 201603    | 11        |
+--------+-----------+-----------+
| 5454   | 201604    | 10        |
+--------+-----------+-----------+
| 5454   | 201605    | 6         |
+--------+-----------+-----------+

查询:

SELECT
    UserID
    ,YearMonth 
    ,SUM(FactCount) AS sumFact
    ,CASE WHEN  sumFact>=10 THEN 1 ELSE 0 END AS FactCount_>=10_Flag
FROM
    tbl
GROUP BY
    UserID
    ,YearMonth 

Answer 1

我认为这实际上是你想要的......

WITH t AS (
  SELECT userid,
    make_date(
      substring(yearmonth::text, 1, 4)::int,
      substring(yearmonth::text, 5, 2)::int,
      1
    ) AS yearmonth,
    factcount
  FROM foo
)
SELECT userid, dategroup, count(*)
FROM (
  SELECT
    userid,
    yearmonth,
    factcount,
    count(is_reset) OVER (PARTITION BY userid ORDER BY yearmonth) AS dategroup
  FROM (
    SELECT
      userid,
      yearmonth,
      factcount,
      CASE WHEN (lag(yearmonth) OVER (PARTITION BY userid ORDER BY yearmonth) + '1 month'::interval)::date <> yearmonth THEN 1 END AS is_reset
    FROM t
    WHERE factcount >= 10
  ) AS t2
) AS t3
GROUP BY userid, dategroup
HAVING count(*) > 1;

那么首先，为了解决201612的问题滚入201701而不是 201613我们需要将这些移动到我们可以使用的地方，即 date

这里我们转换yearmonth日期类型，

SELECT userid,
  make_date(
   substring(yearmonth::text, 1, 4)::int,
    substring(yearmonth::text, 5, 2)::int,
    1
  ) AS yearmonth,
  factcount
FROM foo

我把它放在 CTE 中是因为

• 您不应该像文本 YYYYMM 这样存储日期
• 这是我在开始繁重的工作以解决这个孤岛和缺口问题之前所做的类型修改。

接下来我们从内到外工作..

SELECT
  userid,
  yearmonth,
  factcount,
  CASE WHEN (lag(yearmonth) OVER (PARTITION BY userid ORDER BY yearmonth) + '1 month'::interval)::date <> yearmonth THEN 1 END AS is_reset
FROM t
WHERE factcount >= 10

我们来了

• 只选择带有 factcount>10 的行.您正在运行的此统计信息会忽略其他统计信息。
• 创建一个返回 1 的新虚拟列如果datemonth不成功(在)前一行的 datemonth 之后专栏。

这将返回一个这样的集合，

 userid | yearmonth  | factcount | is_reset 
--------+------------+-----------+----------
   5454 | 2016-03-01 |        11 |         
   5454 | 2016-04-01 |        10 |         
   9987 | 2016-03-01 |        12 |         
   9987 | 2016-05-01 |        19 |        1

然后我们再次包装它，并且count()我们的 is_reset

count(is_reset) OVER (PARTITION BY userid ORDER BY yearmonth) AS dategroup

这将返回一个这样的集合，

 userid | yearmonth  | factcount | dategroup 
--------+------------+-----------+-----------
   5454 | 2016-03-01 |        11 |         0
   5454 | 2016-04-01 |        10 |         0
   9987 | 2016-03-01 |        12 |         0
   9987 | 2016-05-01 |        19 |         1

现在我们

• 分组依据 userid , 和 dategroup
• 选择计数(*)。

这会向您显示所有连续几个月的用户 factcount>10不分年份。

 userid | dategroup | count 
--------+-----------+-------
   5454 |         0 |     2

而且，作为额外的奖励，因为无论如何它都必须完成工作，它会告诉您

• 他们有多少个连续的月份 factcount > 10
• 如果他们有超过两个连续月份的其他组且事实计数>10。即，如果他们有 1 月 - 2 月 - 3 月和 10 月、12 月的事实计数为 11 怎么办？

所以你可能会看到类似的东西，

 userid | dategroup | count 
--------+-----------+-------
   5454 |         0 |     3
   5454 |         1 |     2

但是，我认为出于您的目的，您可以使用该输出来做任何您想做的事情。即，SELECT DISTINCT userid然后 JOIN到主表，如果所有用户的所有行都有两个或多个连续的月份 factcount > 10 .