postgresql 在SQL中使用时间戳细化组

vuktfyat 于 2023-03-01 发布在 PostgreSQL

关注(0)|答案(1)|浏览(141)

假设我们有一个表master，其中包含列column1、column2、column3和timestamp以及一个查询

select
  column1,
  column2,
  count(*) as CNT
from
  master
group by
  column1,
  column2

现在，我想对上面的划分进行如下改进：我们按照时间戳从最老到最年轻对每个组进行排序，在我的示例中，每个组必须具有以下形式

column3 | column3 = 'A' | timestamp
X         FALSE           t1
A         TRUE            t2
A         TRUE            t3
Y         FALSE           t4
Z         FALSE           t5
A         TRUE            t6
A         TRUE            t7
A         TRUE            t8
A         TRUE            t9

也就是说，ti > ti+1，每个组都以FALSE开始。在查询中，我想进一步将每个组划分为单独的组，如下所示。给定上面的单个示例组，我们应该得到3个组：
一个一个二个一个一个一个三个一个一个一个一个一个四个一个
也就是说，每个组都从一个FALSE开始，然后是所有连续的TRUE，直到组中的下一个FALSE。

postgresql

来源：https://stackoverflow.com/questions/75582978/refinement-of-group-by-using-timestamp-in-sql

1条答案

按热度按时间

qmelpv7a1#

如果问题是关于你展示的表格。可以建议以下解决方案。我做了一个类似你的表格：

column3 | A |     time_stamp
---------+---+---------------------
 X       | f | 2021-01-01 00:00:00
 A       | t | 2021-01-01 02:00:00
 A       | t | 2021-01-01 03:00:00
 Y       | f | 2021-01-01 06:00:00
 Z       | f | 2021-01-01 07:00:00
 A       | t | 2021-01-01 04:00:00
 A       | t | 2021-01-01 08:30:00
 A       | t | 2021-01-01 08:45:00
 A       | t | 2021-01-01 07:15:00

WITH vars包含两个子查询。如果CASE“A”= FALSE，我将它的值设置为1，否则为0。结果是“case”列。在下面的子查询中，计算“case”的累计和。结果是“total”列。

WITH vars as (
SELECT * FROM
(SELECT *,
    SUM("case") OVER (ORDER BY time_stamp asc) as total
FROM 
(
 SELECT "column3","A","time_stamp",
 CASE WHEN "A" = FALSE THEN 1
 WHEN "A"= TRUE THEN 0
 ELSE 0
 END
 FROM master
)
pseudo1
)
pseudo2
)

SELECT * 
FROM vars
--WHERE total = 1
;

获取以下数据：

column3 | A |     time_stamp      | case | total
---------+---+---------------------+------+-------
 X       | f | 2021-01-01 00:00:00 |    1 |     1
 A       | t | 2021-01-01 02:00:00 |    0 |     1
 A       | t | 2021-01-01 03:00:00 |    0 |     1
 A       | t | 2021-01-01 04:00:00 |    0 |     1
 Y       | f | 2021-01-01 06:00:00 |    1 |     2
 Z       | f | 2021-01-01 07:00:00 |    1 |     3
 A       | t | 2021-01-01 07:15:00 |    0 |     3
 A       | t | 2021-01-01 08:30:00 |    0 |     3
 A       | t | 2021-01-01 08:45:00 |    0 |     3

如果我们取消对行“--WHERE total = 1”的注解，我们将得到以下行：

column3 | A |     time_stamp
---------+---+----------------
 X       | f | 2021-01-01 00:0
 A       | t | 2021-01-01 02:0
 A       | t | 2021-01-01 03:0
 A       | t | 2021-01-01 04:0

赞(0）回复(0）举报 2023-03-01

我来回答

postgresql 在SQL中使用时间戳细化组

1条答案

相关问题

热门标签

最新问答