Агрегировать столбец по условию
Мне нужно написать отчет о работоспособности домашней страницы. В доступной мне таблице у меня 2 столбца. Первый - это статус домашней страницы (0 - офлайн, 1 - онлайн), второй - продолжительность этого статуса в секундах. Примерная таблица может выглядеть так:
-------------------------
| Status | Duration |
-------------------------
| 0 | 50 |
-------------------------
| 1 | 10 |
-------------------------
| 1 | 20 |
-------------------------
| 1 | 50 |
-------------------------
| 0 | 50 |
-------------------------
| 0 | 50 |
-------------------------
| 1 | 20 |
-------------------------
В моем отчете это выглядит не очень хорошо, потому что одна и та же статистика должна быть объединена в одну строку, а не отображаться в виде нескольких строк, как это:
-------------------------
| Status | Duration |
-------------------------
| 0 | 50 |
-------------------------
| 1 | 80 |
-------------------------
| 0 | 100 |
-------------------------
| 1 | 20 |
-------------------------
Есть ли способ добиться этого с помощью PostgreSQL?
Используйте group by duration
Вам понадобится еще один столбец в таблице, либо последовательность чисел, либо отметка времени, чтобы указать прогресс статуса. База данных не хранит строки в каком-либо определенном порядке. Таким образом, без такого столбца невозможно узнать изменение значения статуса.
Проверьте мой ответ и дайте мне знать, что это помогло. Также прочтите: stackoverflow.com/help/someone-answers
Ответы 2
Эта агрегация может быть достигнута с помощью оконных функций и группировки:
select max(status) status, sum(duration) duration from (
select status, duration, sum(case when status <> par then 1 else 0 end) over (order by id) wf from (
select id, status, duration, lag(status, 1) over () par from test
) a order by id
) a group by wf order by wf
Вам просто нужно правильно настроить порядок в оконной функции.
Данные испытаний:
create table test (status int, duration int, id bigserial primary key);
insert into test (status, duration) values (0, 50);
insert into test (status, duration) values (1, 10);
insert into test (status, duration) values (1, 20);
insert into test (status, duration) values (1, 50);
insert into test (status, duration) values (0, 50);
insert into test (status, duration) values (0, 50);
insert into test (status, duration) values (1, 20);
Вывод как вы хотели.
Как я уже сказал, вам понадобится колонка id/datetime для отслеживания прогресса.
Только тогда вы сможете использовать функцию LEAD/LAG или метод ТАБИБИТОЗАН для этого сценария.
Настройка схемы PostgreSQL 9.6:
CREATE TABLE t
(id INT,Status int, Duration int)
;
INSERT INTO t
(id,Status, Duration)
VALUES
(1,0, 50),
(2,1, 10),
(3,1, 20),
(4,1, 50),
(5,0, 50),
(6,0, 50),
(7,1, 20)
;
Запрос 1:
SELECT STATUS
,Sum(duration)
FROM (
SELECT t.*
,row_number() OVER (
ORDER BY id
) - row_number() OVER (
PARTITION BY STATUS ORDER BY id
) AS seq
FROM t
) s
GROUP BY STATUS
,seq
ORDER BY max(id)
| status | sum |
|--------|-----|
| 0 | 50 |
| 1 | 80 |
| 0 | 100 |
| 1 | 20 |
Плюс к методу Табибитозана, это очень интересно.
попробуйте использовать
group by