统计 PostgreSQL 中每个位置每小时的出现次数

编辑以解决这个问题（强调我的），这与原始请求不同：

在表格中（结果中不必包含 0个航道的航道小时组合） ：

主键很重要

但在我们开始之前，我们需要确保您在数据上定义了正确的主键，即(Boat_Id,Timestamp). 创建它给了我们两件事：

1. 不合格的记录被拒绝（一个Boat不能同时在两个地方）
2. 一种 B 树，用于Boat使用分析/窗口函数以外的方法有效地定位每个记录的先前记录

获得段落

要确定是否发生了段落，我们需要知道每个 的最后位置Boat，我们通过相关子查询搜索最大Timestamp小于当前的条目来获得该位置Timestamp。由于我们只对Boats已移动的 感兴趣，因此Waterways我们可以将它们从结果集中排除。

SELECT
  BD.Waterway_ID
 ,date_trunc('hour',BD.TimeStamp) AS Timestamp
 ,COUNT(*) AS passage_count
FROM
  Boat_Data BD
LEFT JOIN
  Boat_Data PriorBD
    ON PriorBD.Boat_Id = BD.Boat_Id
        AND PriorBD.Timestamp =
          (
            SELECT
              MAX(TimeStamp)
            FROM
              Boat_Data
            WHERE
              Boat_Id = BD.Boat_Id
                AND TimeStamp < BD.Timestamp
         )
WHERE
  BD.Waterway_ID <> PriorBD.Waterway_Id
    OR PriorBD.Waterway_Id IS NULL
GROUP BY
  BD.Waterway_ID
 ,date_trunc('hour',BD.TimeStamp)

或者，您可以使用 Erwin 和 Vérace 所做的分析/窗口函数。我将其作为“第二种解决方案”提供，因为在大多数情况下，分析/窗口函数将强制进行排序¹。对于大量数据（或不同的 RDBMS），这可能比仅使用正确的主键²进行自联接更昂贵。和往常一样，测试。

SELECT
  BD.Waterway_ID
 ,date_trunc('hour',BD.TimeStamp) AS Timestamp
 ,COUNT(*) AS passage_count
FROM
  (
    SELECT
      Boat_Id
     ,Timestamp
     ,Waterway_Id
     ,CASE
        WHEN Waterway_Id <> LAG(Waterway_Id,1,'') OVER (PARTITION BY Boat_Id ORDER BY Timestamp) THEN 1
        ELSE 0
      END AS Passage_Ind
    FROM
      Boat_Data
  ) BD
WHERE
  BD.Passage_Ind = 1
GROUP BY
  BD.Waterway_ID
 ,date_trunc('hour',BD.TimeStamp)
;

此处修改小提琴：http ://sqlfiddle.com/#!17/2cede7/2

¹PARTITION BY在 SQL Server（可能还有其他一些商业平台）中，如果 andORDER BY语句与聚集索引的排序顺序匹配，则窗口/分析函数不会强制进行排序。在 MySQL 中情况并非如此。

²较新的 Postgres 版本允许 INCLUDE 语句强制将指定的非键列添加到 B-Tree。在这种情况下，您可以包含 ，Waterway_Id以便在不触及堆的情况下完成整个查询。

这是被称为Tabibito-san的一类问题的一部分- 非常值得了解！既然我想我已经掌握了你的问题，这个答案已经被高度修改了。

我稍微更改了您的架构 - 我删除了带引号的标识符 - 它们通常是不必要的，只会增加复杂性并使查询不那么清晰。

我还将名为 (boat timestamp) 的字段更改为timestamp，bts因为使用 SQL 关键字作为变量名不是一个好主意——它也使 SQL 难以阅读并干扰调试。

我也只保留数据boat_1- 更容易推理。我使用的数据可以在小提琴和这篇文章的底部找到。

您可以在这里找到小提琴（哦，顺便说一句，请在任何问题中始终包含您的 PostgreSQL 版本）- 对 sqlfiddle.com 不重要（他们只有 9.6），但如果您使用 dbfiddle.uk（更多服务器），它可以是最有帮助的。

修改后的 DDL：

CREATE TABLE boat_data
    (boat_id int, bts timestamp, waterway_id varchar(9))
;

然后我运行了以下查询：

SELECT 
  boat_id, 
  MIN(bts) AS min_time, 
  MAX(bts) AS max_time, 
  waterway_id, 
  MIN(rn) AS min_rn,  
  MAX(rn) AS max_rn   
FROM
(
  SELECT boat_id, bts, waterway_id,
  ROW_NUMBER() 
    OVER 
      (
        PARTITION BY boat_id, waterway_id 
        ORDER BY boat_id, waterway_id
      ) AS rn
  FROM boat_data
  ORDER BY boat_id, waterway_id
) AS tab
GROUP BY boat_id, waterway_id;

结果（为简洁起见）：

boat_id min_time    max_time    waterway_id min_rn  max_rn
1   2019-06-03T10:27:25Z    2019-06-03T10:28:45Z    OSDOK003    1   4
1   2019-06-03T10:29:26Z    2019-06-03T10:29:54Z    OSDOK005    1   4
1   2019-06-03T10:32:26Z    2019-06-03T10:32:26Z    OUDSC001    1   1
1   2019-06-03T10:32:45Z    2019-06-03T10:34:34Z    OUDSC002    1   8
1   2019-06-03T10:30:35Z    2019-06-03T10:30:54Z    OUDSC003    1   3

您可能不想要所有这些数据 - 酌情删除！

有一个“段落”列表，提供了关于它们的所有细节——正如我所说，也许不是必要的？

• 第一行告诉您的是，对于boat_1，它的第一个通道开始于水路OSDOK003并2019-06-03T10:27:25Z结束于2019-06-03T10:28:45Z，并且在该通道期间进行了 4 次测量。

• 然后它在时间 x 进入水路OSDOK005并在时间 y 完成 - 也是 4 次测量。

• 然后对水路进行了1次测量OUDSC001

• 随后在航道上进行了 8 次测量OUDSC002

• 然后最后返回OUDSC003进行 3 次测量。

我已经“目测”了数据，这看起来是正确的！

现在，您可能必须考虑日期 - 在这种情况下，只需添加DATE(bts)和...SELECTGROUP BY

我在小提琴的底部留下了一些“人工制品”，这样你就可以看到（或多或少以相反的顺序）我的想法在哪里——Postgresql 的窗口函数非常强大，非常值得掌握——它们会回报任何努力 10时间超过 - 特别是。ROW_NUMBER() - 看看他们还有 LAG/LEAD (fiddle)...

=========================================

boat_1此答案中使用的数据。

INSERT INTO boat_data
    (boat_id, bts, waterway_id) 
VALUES
    (1, '2019-06-03 10:27:25', 'OSDOK003'),
    (1, '2019-06-03 10:27:54', 'OSDOK003'),
    (1, '2019-06-03 10:28:05', 'OSDOK003'),
    (1, '2019-06-03 10:28:45', 'OSDOK003'),
    (1, '2019-06-03 10:29:26', 'OSDOK005'),
    (1, '2019-06-03 10:29:35', 'OSDOK005'),
    (1, '2019-06-03 10:29:45', 'OSDOK005'),
    (1, '2019-06-03 10:29:54', 'OSDOK005'),
    (1, '2019-06-03 10:30:35', 'OUDSC003'),
    (1, '2019-06-03 10:30:45', 'OUDSC003'),
    (1, '2019-06-03 10:30:54', 'OUDSC003'),
    (1, '2019-06-03 10:32:26', 'OUDSC001'),
    (1, '2019-06-03 10:32:45', 'OUDSC002'),
    (1, '2019-06-03 10:32:55', 'OUDSC002'),
    (1, '2019-06-03 10:33:34', 'OUDSC002'),
    (1, '2019-06-03 10:33:45', 'OUDSC002'),
    (1, '2019-06-03 10:33:54', 'OUDSC002'),
    (1, '2019-06-03 10:34:04', 'OUDSC002'),
    (1, '2019-06-03 10:34:14', 'OUDSC002'),
    (1, '2019-06-03 10:34:34', 'OUDSC002');

假设所有涉及的表列NOT NULL。

您添加的说明使它成为一个更简单的问题。
这仅计算每个段落的第一个小时：

SELECT waterway_id, date_trunc('hour', ts), count(*) AS count
FROM  (
   SELECT waterway_id, ts -- , boat_id
        , lag(waterway_id, 1, '') OVER (PARTITION BY boat_id ORDER BY ts) <> waterway_id AS switch
   FROM   boat_data
   ) sub
WHERE  switch  -- only the first ts of each passage
GROUP  BY 1, 2
ORDER  BY 1, 2;

db<>在这里摆弄

我们只需要考虑每艘船切换水道后的第一排。用窗口函数识别它lag()。用于lag(waterway_id, 1, '')抑制每个分区中第一行的 NULL。（假设空字符串 ( '') 与任何现有的 . 不同。）然后用和 countwaterway_id截断到整小时。date_trunc()瞧。

我最初的解决方案计算每个段落的每小时，这要复杂得多：

SELECT waterway_id, report_ts, count(*) AS count
FROM  (
   SELECT waterway_id
        , generate_series(date_trunc('hour', min(ts))
                        , max(ts)
                        , interval '1 hour') AS report_ts
   FROM  (
      SELECT *
           , count(switch) OVER (PARTITION BY boat_id ORDER BY ts) AS passage
      FROM  (
         SELECT boat_id, ts, waterway_id
              , lag(waterway_id) OVER (PARTITION BY boat_id ORDER BY ts) <> waterway_id OR NULL AS switch
         FROM   boat_data
         ) sub1
      ) sub2
   GROUP  BY boat_id, waterway_id, passage
   ) sub3
GROUP  BY waterway_id, report_ts
ORDER  BY waterway_id, report_ts;

db<>在这里摆弄

有关的：

• 选择最长的连续序列