Sql-запрос для подсчета людей в каждый час отдельно
У меня есть таблица с этими полями:
id | person_id | start_time | end_time | status
Я хочу получить количество людей, которое существует в каждый час в определенный день, что-то вроде псевдокода ниже:
select count(person) from table where dow of end_time=day and end_time >= hour and start_time < hour+1 for hour in working hours of organization and day is a certain day of week
Если бы можно было иметь временную таблицу, которая состоит из часов в день, может быть следующее решение:
select t.h, count(s.id)
from
session s cross join (temperoray table with one column of hours in a day as t)
where
s.start_time < (t.h + 1) and s.end_time > t.h
group by
t.h
Но я не знаю команды, которая могла бы создать временную таблицу по мере необходимости.
Я нашел этот вопрос, который очень похож на то, что я хочу, но все его решение основано на group by, что, как я думаю, не имеет смысла в моем случае, потому что части каждой группы имеют общие элементы, например, человек может считаться человеком в час 11 и человек в часах 12 и 13.
Я надеюсь, что смогу найти способ получить такую таблицу:
hour |number of persons
10 |2
11 |0
12 |3
13 |1
...
Обратите внимание, что в какой-то день может быть нулевое количество людей.
Пример:
id | status | start_time | end_time | branch_id | person_id | session_type
------+--------+----------------------------------+----------------------------------+-----------+-----------+--------------
2675 | FI | 2018-04-23 10:30:50.939693+04:30 | 2018-04-23 12:31:39.340692+04:30 | 1 | 1085 | IN
2676 | FI | 2018-04-23 11:47:06.683374+04:30 | 2018-04-23 13:23:52.659714+04:30 | 1 | 2722 | IN
2677 | FI | 2018-04-23 11:47:59.341765+04:30 | 2018-04-23 13:25:46.339266+04:30 | 1 | 2721 | IN
2678 | FI | 2018-04-23 11:58:34.854222+04:30 | 2018-04-23 13:25:55.08795+04:30 | 1 | 2723 | IN
2679 | FI | 2018-04-23 12:27:58.817234+04:30 | 2018-04-23 13:12:28.278699+04:30 | 1 | 2724 | IN
2680 | FI | 2018-04-23 12:30:36.552407+04:30 | 2018-04-23 12:30:54.088159+04:30 | 1 | 2725 | IN
2681 | FI | 2018-04-23 14:55:50.886725+04:30 | 2018-04-23 16:08:27.076629+04:30 | 1 | 25 | IN
2682 | FI | 2018-04-23 15:06:30.443347+04:30 | 2018-04-23 15:52:20.128546+04:30 | 1 | 2653 | IN
2683 | FI | 2018-04-23 15:21:57.979387+04:30 | 2018-04-23 16:16:09.289267+04:30 | 1 | 2580 | IN
2684 | FI | 2018-04-23 15:26:18.057999+04:30 | 2018-04-23 16:02:44.704133+04:30 | 1 | 2726 | IN
2685 | FI | 2018-04-23 16:50:10.2957+04:30 | 2018-04-23 17:23:01.732404+04:30 | 1 | 2727 | IN
2686 | FI | 2018-04-23 16:52:28.474299+04:30 | 2018-04-23 17:23:51.013318+04:30 | 1 | 2728 | IN
2687 | FI | 2018-04-23 16:58:05.796563+04:30 | 2018-04-23 17:33:03.259335+04:30 | 1 | 1646 | IN
2688 | FI | 2018-04-23 17:50:02.738009+04:30 | 2018-04-23 18:43:27.152203+04:30 | 1 | 2729 | IN
2689 | FI | 2018-04-23 18:47:12.19468+04:30 | 2018-04-23 19:25:46.606731+04:30 | 1 | 2730 | IN
2690 | FI | 2018-04-23 19:18:32.922065+04:30 | 2018-04-23 20:11:26.703693+04:30 | 1 | 2408 | IN
2691 | FI | 2018-04-23 19:18:53.133712+04:30 | 2018-04-23 19:56:47.702305+04:30 | 1 | 2409 | IN
2692 | FI | 2018-04-23 19:21:00.348889+04:30 | 2018-04-23 20:24:25.882451+04:30 | 1 | 2731 | IN
2693 | FI | 2018-04-23 19:30:05.908247+04:30 | 2018-04-23 20:12:36.627888+04:30 | 1 | 2591 | IN
2694 | FI | 2018-04-23 19:36:02.700379+04:30 | 2018-04-23 20:13:35.146002+04:30 | 1 | 2732 | IN
2695 | FI | 2018-04-23 19:50:15.13214+04:30 | 2018-04-23 20:09:37.168147+04:30 | 1 | 2491 | IN
2696 | FI | 2018-04-23 19:51:54.754169+04:30 | 2018-04-23 20:09:59.029376+04:30 | 1 | 2733 | IN
2697 | FI | 2018-04-23 19:53:13.529475+04:30 | 2018-04-23 20:09:49.229139+04:30 | 1 | 2734 | IN
2698 | FI | 2018-04-23 19:59:27.70488+04:30 | 2018-04-23 20:21:47.862433+04:30 | 1 | 1762 | IN
2699 | FI | 2018-04-23 19:59:57.86605+04:30 | 2018-04-23 20:22:05.171377+04:30 | 1 | 1761 | IN
2700 | FI | 2018-04-23 20:24:21.212784+04:30 | 2018-04-23 20:47:31.854373+04:30 | 1 | 2735 | IN
2701 | FI | 2018-04-23 21:58:57.308547+04:30 | 2018-04-23 22:43:20.075321+04:30 | 1 | 1705 | IN
2702 | FI | 2018-04-23 21:59:44.974384+04:30 | 2018-04-23 22:43:45.946989+04:30 | 1 | 1704 | IN
2703 | FI | 2018-04-23 22:10:20.991216+04:30 | 2018-04-23 22:40:51.16409+04:30 | 1 | 2711 | IN
И я, чтобы получить результаты на этот день, как показано ниже:
hour | number
10 | 1
11 | 4
12 | 6
13 | 4
14 | 1
15 | 4
16 | 6
17 | 4
18 | 2
19 | 11
20 | 10
21 | 2
22 | 3
Я не понимаю, как это можно сделать, не подсчитывая количество людей (т.е. используя group by).
@DanielVaca Я помещаю несколько примеров своих данных в определенную дату и вручную вычисляю количество людей в каждый час.
Ответы 4
Посмотрите на этот статья, который проходит через создание таблицы Temp в PostgreSQL (я человек SQL-сервера).
Имейте один столбец, в котором указаны часы дня, и я думаю, что ваш метод присоединения его к вашей таблице сработает.
Это также зависит от ваших данных в этой таблице. Можете ли вы предоставить данные первой строки, чтобы понять, как они выглядят? Возможно, вам даже не понадобится временная таблица.
Если я правильно понимаю
вам нужно использовать функцию generate_series, чтобы создать 24 часа, а затем left join.
Вы можете добавить условие к пункту Where.
Получить по Час
SELECT gs.hours,
Sum(
CASE
WHEN start_time IS NOT NULL THEN 1
WHEN end_time IS NOT NULL THEN 1
ELSE 0
END ) AS "count"
FROM (
SELECT hours
FROM Generate_series(1,24) AS gs(hours) ) gs
LEFT JOIN
(
SELECT *,
Generate_series(start_time::timestamp, end_time::timestamp, '1 hours') invhour
FROM t )t
ON gs.hours = To_char(t.invhour,'HH24')::integer
GROUP BY gs.hours
Получить по Час и Дата
SELECT To_char(t.invhour,'yyyy-MM-dd') AS "dates",
gs.hours,
Sum(
CASE
WHEN start_time IS NOT NULL THEN 1
WHEN end_time IS NOT NULL THEN 1
ELSE 0
END ) AS "count"
FROM (
SELECT hours
FROM Generate_series(1,24) AS gs(hours) ) gs
LEFT JOIN
(
SELECT *,
Generate_series(start_time::timestamp, end_time::timestamp, '1 hours') invhour
FROM t )t
ON gs.hours = To_char(t.invhour,'HH24')::integer
GROUP BY gs.hours,
to_char(t.invhour,'yyyy-MM-dd')
sqlfiddle: http://sqlfiddle.com/#!17/717fa/1
Вам нужно будет создать набор на 24 часа, затем выполнить левое присоединение и группу по часам.
Кто-то здесь предложил использовать generate_series в подзапросе, но лично я считаю, что рекурсивные ctes немного лучше для создания диапазонов. Таким образом, вы можете оставить серию вне основного запроса, что немного упростит понимание и поддержку.
; WITH RECURSIVE Hours AS
(
SELECT 1 AS hour
UNION ALL
SELECT hour + 1 FROM Hours WHERE Hour < 24
)
SELECT hour, COUNT(person_id)
FROM Hours
LEFT JOIN T on hour BETWEEN extract(hour from start_time) AND extract(hour from end_time)
GROUP BY hour
ORDER BY hour
Используя generate_series из Ответ Д-Ши, я получаю следующие решения.
Это первое решение показывает все часы, когда работал хотя бы один человек.
select Hour, count(1) as "Users"
from generate_series(1,24) as gs (Hour)
join Log as l
on date_part('hour',l.TimeFrom) <= gs.Hour
and date_part('hour',l.TimeTo) >= gs.Hour
group by Hour
order by Hour;
Если вам также нужны часы, когда никто не работал, используйте left join.
Это требует изменения count(), чтобы он считал только при обнаружении записи.
select Hour, count(case when l.UserId is not null then 1 end) as "Users"
from generate_series(1,24) as gs (Hour)
left join Log as l
on date_part('hour',l.TimeFrom) <= gs.Hour
and date_part('hour',l.TimeTo) >= gs.Hour
group by Hour
order by Hour;
См. этот скрипт SQL для некоторых примеров данных и вывода.
Ваш ответ кажется правильным, но левое соединение для меня не работает, и я получаю только строки, которые совпадают в обеих таблицах, и никаких строк с нулевым значением второй таблицы. Возможно ли это, потому что я не позволяю некоторым значениям второй таблицы быть нулевыми?
Вы случайно не используете левую объединенную таблицу в своем предложении where? Это означает, что запись должен будет найдена во второй таблице или предложение where не может быть проверено. Это неявно превращает левое внешнее соединение во внутреннее соединение. Как это: sqlfiddle.com/#!17/014e8/18
Да, ваша догадка верна, наконец, этот запрос: sqlfiddle.com/#!17/b8444/8 у меня работает.
Не могли бы вы предоставить образцы данных и ожидать результата? Это действительно поможет