代码之家 › 专栏 › 技术社区 › gudvinr

根据条件统计顺序记录数

postgresql-9.6 gaps-and-islands postgresql sql

0

gudvinr · 技术社区 · 8 年前

我有一个表,其中包含最初按时间戳排序的行数:

+----+------------+-----+
| id | date       | foo |
+----+------------+-----+
| 1  | 2017-12-28 | abc |
+----+------------+-----+
| 1  | 2017-12-27 | abc |
+----+------------+-----+
| 2  | 2017-12-27 | xyz |
+----+------------+-----+
| 2  | 2017-12-26 | xyz |
+----+------------+-----+
| 2  | 2017-12-25 | abc |
+----+------------+-----+
| 1  | 2017-12-25 | abc |
+----+------------+-----+
| 2  | 2017-12-25 | abc |
+----+------------+-----+

我想得到 顺序的 具有相同 foo 对于不同的 id 就像这样:

+----+-----+-------+
| id | foo | count |
+----+-----+-------+
| 1  | abc | 2     |
+----+-----+-------+
| 2  | xyz | 2     |
+----+-----+-------+
| 2  | abc | 1     |
+----+-----+-------+
| 1  | abc | 1     |
+----+-----+-------+
| 2  | abc | 1     |
+----+-----+-------+

所以 here 是SQLFIDLE与构建的架构。

窗口函数看起来像是解决此类问题的关键,但它并没有像我使用的那样工作得很好。

我很乐意得到任何帮助或至少一些有用的提示。 MySQL上有一些与此相关的问题,但它们并没有太大帮助。

1 回复 | 直到 8 年前

1

Kaushik Nayak 8 年前

首先,非常感谢您的SQLFIDLE。

使用标准方法(Tabibitosan)解决间隙和孤岛问题,使用 row_number()

SQL Fiddle

PostgreSQL 9.6架构设置 :

create table bar (
  id   bigint not null,
  date timestamp without time zone,
  foo  text
);

insert into bar (id, date, foo) values
  (1, '2017-12-28 17:54:02', 'abc'),
  (1, '2017-12-28 17:53:30', 'abc'),
  (2, '2017-12-28 17:50:13', 'xyz'),
  (2, '2017-12-28 17:44:35', 'xyz'),
  (2, '2017-12-28 17:30:00', 'abc'),
  (1, '2017-12-28 17:25:15', 'abc'),
  (2, '2017-12-28 17:21:20', 'abc');

查询1 :

SELECT MAX (id) AS id,
         foo,
         COUNT (*) AS "count"
    FROM (SELECT b.*,
                   ROW_NUMBER () OVER (ORDER BY date DESC)
                 - ROW_NUMBER () OVER (PARTITION BY id ORDER BY date DESC)
                    seq
            FROM bar b) t
GROUP BY foo, seq, id
ORDER BY MAX(DATE) DESC

Results :

| id | foo | count |
|----|-----|-------|
|  1 | abc |     2 |
|  2 | xyz |     2 |
|  2 | abc |     1 |
|  1 | abc |     1 |
|  2 | abc |     1 |