sql - 如何区分没有 ID 的日志以在 SQL 上单独聚合它们？

Question

我有一个(假设的)设备可以记录其电池的消耗事件。它有几个插槽，每天记录每个插槽中电池消耗的百分比。这是数据的样子:

CREATE TABLE batteries
(slot integer, day date, percentage integer);

INSERT INTO batteries
(slot, day, percentage)
VALUES
(0, '2020-05-08', 96),
(0, '2020-05-09', 96),
(0, '2020-05-10', 97),
(0, '2020-05-11', 97),
(0, '2020-05-12', 97),
(0, '2020-05-13', null),
(0, '2020-05-14', 95),
(0, '2020-05-15', 96),
(0, '2020-05-16', null),
(0, '2020-05-17', 1),
(0, '2020-05-18', 2),
(1, '2020-05-08', 10),
(1, '2020-05-09', 10),
(1, '2020-05-10', 10);

日志显示在插槽 0 中，几乎完全消耗的电池已于 5 月 13 日更换为另一块旧电池，然后在 5 月 16 日更换为新电池。插槽中的电池 1总是报告 10% 的使用率。

我需要识别每个单独的电池，它报告的最后一个值以及它报告这样一个值的第一个和最后一个日期。所以这是我想要得到的输出:

slot  min_date      max_date      percentage  sequence
------------------------------------------------------
0     '2020-05-10'  '2020-05-12'  97          0
0     '2020-05-15'  '2020-05-15'  96          1
0     '2020-05-18'  '2020-05-18'  2           2
1     '2020-05-08'  '2020-05-10'  10          0

电池的最后已知值 0在插槽中 0是 97 ，它于 5 月 10 日至 5 月 12 日报告；

电池的最后已知值 1在插槽中 0是 96 ，它仅在 5 月 15 日报告；

电池的最后已知值 2 (当前)在插槽 0是 2 ，它仅在 5 月 18 日报道；

电池的最后已知值 0 (当前)在插槽 1是 10 ，它于 5 月 8 日至 5 月 10 日报告。

我的主要问题是如何在没有电池 ID 的情况下获取每个电池的最小和最大日期。在这个例子中，如果我按槽和百分比分组来获取日期，我会得到错误的电池最小日期 1插槽 0 ，因为之前该插槽中还有另一块具有相同百分比的电池。

有没有一种方法可以在不进行后处理的情况下在 SQL 查询中获得此结果？

Answer 1

如果我正确理解数据，当值为 NULL 时，您就知道有一块新电池。 .如果这是指示，那么您可以通过计算NULL 的数量来计算序列。值到每一行(使用累积总和)。

您还有一个额外的步骤来获取最后一个值然后聚合:

select slot,
       min(day) filter (where percentage = last_percentage),
       max(day), last_percentage,
       sequence
from (select b.*,
             first_value(percentage) over (partition by slot, sequence order by day desc) as last_percentage
      from (select b.*,
                   count(*) filter (where percentage is null) over (partition by slot order by day) as sequence
            from batteries b
           ) b
       where percentage is not null
     ) b
group by slot, sequence, last_percentage
order by slot, sequence;

Here是一个db<> fiddle 。

在 Redshift 中，您只需使用 case表达式或 bool 值:

select slot,
       min(case when percentage = last_percentage then day end),
       max(day), last_percentage,
       sequence
from (select b.*,
             first_value(percentage) over (partition by slot, sequence order by day desc) as last_percentage
      from (select b.*,
                   sum( (percentage is null)::int ) over (partition by slot order by day) as sequence
            from batteries b
           ) b
       where percentage is not null
     ) b
group by slot, sequence, last_percentage
order by slot, sequence;