Skip to content

Commit dd3d24d

Browse files
sofakingworldsofakingworld
committed
PostgresSQL строим отчет в маленьком магазине
1 parent 8057901 commit dd3d24d

8 files changed

Lines changed: 267 additions & 0 deletions

‎README.md‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7,3 +7,6 @@
77

88
* [ТОDO как причина технического долга](/posts/todo_problem.md)
99
* [Битовые карты ](/posts/bitmaps.md)
10+
11+
## Базы данных
12+
* [PostgresSQL пример построения отчета в маленьком магазине](/posts/sql_lesson.md)

‎images/sql_lesson:art_1.png‎

317 KB
Loading

‎images/sql_lesson:art_2.png‎

314 KB
Loading

‎images/sql_lesson:report_sample.png‎

99.6 KB
Loading

‎images/sql_lesson:risk_factors.png‎

701 KB
Loading

‎images/sql_lesson:table.png‎

12.1 KB
Loading

‎images/sql_lesson:table_data.png‎

452 KB
Loading

‎posts/sql_lesson.md‎

Lines changed: 264 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,264 @@
1+
2+
# SQL на практике
3+
4+
> P.S. в статье запросы написаны на postgresql
5+
6+
# Итак, для начала ознакомимся с предметной областью
7+
8+
У нас есть маленький магазичник, но довольно проходной, потому что торгует всем, начиная от **ПИВА** и заканчивая **ВОДКОЙ**…
9+
10+
![](/images/sql_lesson:art_1.png)
11+
12+
Владельцу понадобилось узнать, сколько людей заходит в магазин и в какое время, чтобы скоординировать смены кассиров и поставки. Во время максимальной посещаемости нужен выход дополнительного кассира, а во время минимальной можно заниматься залом и раскладывать поступивший товар.
13+
14+
Для учета количества посетителей на магнитные рамки у входа было установлено **2 лазерных датчика.**
15+
16+
![](/images/sql_lesson:art_2.png)
17+
18+
Данные датчики при каждом срабатывании пишут соотвествующие данные в таблицу **sensor_triggers**, где **sensor_id** — **идентификтор датчика**, а **created_at** — **время срабатывания**.
19+
20+
![Схема таблицы](/images/sql_lesson:table.png)*Схема таблицы*
21+
22+
У датчика #1 — **sensor_id = 1**, а у датчика #2 — **sensor_id = 2**
23+
24+
По очередности срабатывания датчиков можно сделать вывод, вошел человек в магазин или вышел.
25+
26+
## Данные в таблице выглядят так:
27+
28+
![](/images/sql_lesson:table_data.png)
29+
30+
Записи #3, #4, #5 — **Лжесрабатывания**, потому что нарушена очередность срабатывания датчиков, и на основании этих данных нельзя сделать вывод, вошел человек или вышел.
31+
32+
## Возможные причины лжесрабатываний
33+
34+
![](/images/sql_lesson:risk_factors.png)
35+
36+
------
37+
38+
# C предметной областью мы закончили, теперь переходим к отчету.
39+
40+
Для начала, **уточняем** у заказчика что он хочет видеть, по возможности просим предоставить пример. Нам повезло, владелец показал, что он хочет видеть.
41+
42+
**“Отчет по проходимости магазина с почасовой группировкой”**
43+
44+
![](/images/sql_lesson:report_sample.png)
45+
46+
**Выводы из примера:**
47+
1) В отчете должны отображаться промежутки с нулевой посещаемостью;
48+
2) В отчете нужен итог за день;
49+
3) Возможна погрешность в данных на вход и выход.
50+
51+
## Получается, что нам необходимо:
52+
53+
1. Из-за того, что в отчете необходимо показывать промежутки времени без посетителей надо сгенерировать возможные промежутки времени, чтобы к ним JOIN'ить данные по входу и выходу;
54+
55+
2. Обработать данные из таблицы **sensor_triggers**, чтобы в них прослеживалось количество входящих и выходящих людей, а также надо отбросить ложные срабатывания;
56+
57+
3. Добавить строку с итогами.
58+
59+
----
60+
61+
# Написание SQL запроса
62+
63+
## Примерная структура псевдозапроса
64+
65+
Полезно в виде черновика или наброска продумать структуру будущего запроса, тогда можно будет определиться в количестве запросов/подзапросов/join'ов и общей сложности.
66+
67+
```SQL
68+
;WITH hours as (
69+
...
70+
),
71+
incomes as (
72+
SELECT *
73+
FROM sensor_triggers
74+
WHERE ...
75+
GROUP BY hour(created_at)
76+
),
77+
outcomes as (
78+
SELECT *
79+
FROM sensor_triggers
80+
WHERE ...
81+
GROUP BY hour(created_at)
82+
),
83+
rows as (
84+
SELECT *
85+
FROM hours
86+
LEFT JOIN incomes on incomes.hour = hours.hour
87+
LEFT JOIN outcomes on outcomes.hour = hours.hour
88+
),
89+
final_row as (
90+
SELECT
91+
'Итог' literal
92+
sum(rows.incomes),
93+
sum(rows.outcomes)
94+
FROM rows
95+
)
96+
SELECT * FROM rows
97+
UNION
98+
select * FROM final_row
99+
```
100+
101+
* **hours** — временные интервалы
102+
* **incomes** — количество вошедших людей
103+
* **outcomes** — количество вышедших людей
104+
* **rows** — данные сгруппированные по часам
105+
* **final_rows** — агрегированные **rows**
106+
107+
108+
## Генерируем промежутки времени
109+
110+
Не будем особо заморачиваться с автоматической генерацией строк, просто перечислим возможные варианты. Для генерации можно использовать рекурсивный WITH **(WITH RECURSIVE)**.
111+
112+
```SQL
113+
;WITH hours as (
114+
SELECT 0 hour_id, '00:00 - 01:00' hour_literal UNION
115+
SELECT 1 hour_id, '01:00 - 02:00' hour_literal UNION
116+
SELECT 2 hour_id, '02:00 - 03:00' hour_literal UNION
117+
SELECT 3 hour_id, '03:00 - 04:00' hour_literal UNION
118+
SELECT 4 hour_id, '04:00 - 05:00' hour_literal UNION
119+
SELECT 5 hour_id, '05:00 - 06:00' hour_literal UNION
120+
SELECT 6 hour_id, '06:00 - 07:00' hour_literal UNION
121+
SELECT 7 hour_id, '07:00 - 08:00' hour_literal UNION
122+
SELECT 8 hour_id, '08:00 - 09:00' hour_literal UNION
123+
SELECT 9 hour_id, '09:00 - 10:00' hour_literal UNION
124+
SELECT 10 hour_id, '10:00 - 11:00' hour_literal UNION
125+
SELECT 11 hour_id, '11:00 - 12:00' hour_literal UNION
126+
SELECT 12 hour_id, '12:00 - 13:00' hour_literal UNION
127+
SELECT 13 hour_id, '13:00 - 14:00' hour_literal UNION
128+
SELECT 14 hour_id, '14:00 - 15:00' hour_literal UNION
129+
SELECT 15 hour_id, '15:00 - 16:00' hour_literal UNION
130+
SELECT 16 hour_id, '16:00 - 17:00' hour_literal UNION
131+
SELECT 17 hour_id, '17:00 - 18:00' hour_literal UNION
132+
SELECT 18 hour_id, '18:00 - 19:00' hour_literal UNION
133+
SELECT 19 hour_id, '19:00 - 20:00' hour_literal UNION
134+
SELECT 20 hour_id, '20:00 - 21:00' hour_literal UNION
135+
SELECT 21 hour_id, '21:00 - 22:00' hour_literal UNION
136+
SELECT 22 hour_id, '22:00 - 23:00' hour_literal UNION
137+
SELECT 23 hour_id, '23:00 - 00:00' hour_literal
138+
ORDER BY 1
139+
)
140+
```
141+
**hour_literal** соответсвтует первому столбцу отчета, а **hour_id** необходим для соединения с данными о входе и выходе посетителей.
142+
143+
## Обрабатываем данные таблицы **sensor_triggers**
144+
145+
1. Данные необходимо сгруппировать по часам;
146+
147+
1. В данном случае проще не отбрасывать **лжесрабатывания**, а определить набор правил, когда засчитывать срабатывания как вход или выход. Будем считать, что человек с минимально возможной скоростью передвижения задействует два датчика за 5 секунд, тогда нам остается выбрать из таблицы последовательно задействованные датчики с максимальным периодом между срабатываниями = 5с. Тогда нам достаточно найти запись одного датчика, и второго с указанной разницей во времени.
148+
149+
## **Создаем incomes и outcomes**
150+
```SQL
151+
;WITH incomes as (
152+
-- группируем по часу, сразу считаем количество проходов
153+
SELECT date_part as , count(*)
154+
FROM
155+
(SELECT
156+
DATE_PART('hour', started.created_at) date_part,
157+
ROW_NUMBER() OVER (PARTITION BY started.id) rn
158+
FROM sensor_triggers started
159+
JOIN sensor_triggers ended
160+
-- Проверяем, что срабатывание между датчиками < 5 секунд
161+
ON ended.created_at - started.created_at BETWEEN '00:00:00'::time AND '00:00:05'::time
162+
-- Определяем, от какого к какому сенсерому ведем отсчет
163+
AND started.sensor_id = 1 AND ended.sensor_id = 2
164+
-- В данном случае считаем количество "приходящих"
165+
-- посетителей (от датчика 1 к датчику 2)
166+
) as temporally
167+
-- Если у датчика 2 были лжесрабатывания, то при джойне
168+
-- будут лишние записи, для этого, поэтому мы ввели подсчет
169+
-- номера строки в группе, т.к. нам достаточно получить
170+
-- первую запись в группировке
171+
WHERE rn = 1
172+
GROUP BY date_part
173+
)
174+
SELECT * FROM incomes;
175+
```
176+
177+
## **outcomes** выглядит абсолютно так же, только меняется порядок датчиков.
178+
----
179+
## Собираем отчет из всех подготовленных данных
180+
181+
```SQL
182+
;WITH hours as (
183+
SELECT 0 hour_id, '00:00 - 01:00' hour_literal UNION
184+
SELECT 1 hour_id, '01:00 - 02:00' hour_literal UNION
185+
SELECT 2 hour_id, '02:00 - 03:00' hour_literal UNION
186+
SELECT 3 hour_id, '03:00 - 04:00' hour_literal UNION
187+
SELECT 4 hour_id, '04:00 - 05:00' hour_literal UNION
188+
SELECT 5 hour_id, '05:00 - 06:00' hour_literal UNION
189+
SELECT 6 hour_id, '06:00 - 07:00' hour_literal UNION
190+
SELECT 7 hour_id, '07:00 - 08:00' hour_literal UNION
191+
SELECT 8 hour_id, '08:00 - 09:00' hour_literal UNION
192+
SELECT 9 hour_id, '09:00 - 10:00' hour_literal UNION
193+
SELECT 10 hour_id, '10:00 - 11:00' hour_literal UNION
194+
SELECT 11 hour_id, '11:00 - 12:00' hour_literal UNION
195+
SELECT 12 hour_id, '12:00 - 13:00' hour_literal UNION
196+
SELECT 13 hour_id, '13:00 - 14:00' hour_literal UNION
197+
SELECT 14 hour_id, '14:00 - 15:00' hour_literal UNION
198+
SELECT 15 hour_id, '15:00 - 16:00' hour_literal UNION
199+
SELECT 16 hour_id, '16:00 - 17:00' hour_literal UNION
200+
SELECT 17 hour_id, '17:00 - 18:00' hour_literal UNION
201+
SELECT 18 hour_id, '18:00 - 19:00' hour_literal UNION
202+
SELECT 19 hour_id, '19:00 - 20:00' hour_literal UNION
203+
SELECT 20 hour_id, '20:00 - 21:00' hour_literal UNION
204+
SELECT 21 hour_id, '21:00 - 22:00' hour_literal UNION
205+
SELECT 22 hour_id, '22:00 - 23:00' hour_literal UNION
206+
SELECT 23 hour_id, '23:00 - 00:00' hour_literal
207+
ORDER BY 1
208+
),
209+
incomes as (
210+
SELECT date_part, count(*)
211+
FROM
212+
(
213+
SELECT
214+
DATE_PART('hour', started.created_at) date_part,
215+
ROW_NUMBER() OVER (PARTITION BY started.id) rn
216+
FROM sensor_triggers started
217+
JOIN sensor_triggers ended
218+
ON ended.created_at - started.created_at BETWEEN '00:00:00'::time AND '00:00:05'::time
219+
AND started.sensor_id = 1 AND ended.sensor_id = 2
220+
-- Параметр "дата" формирования отчета
221+
AND started.created_at::date = '2019.06.09'
222+
) as temporally
223+
WHERE rn = 1
224+
GROUP BY date_part
225+
),
226+
outcomes as (
227+
SELECT date_part, count(*)
228+
FROM
229+
(
230+
SELECT
231+
DATE_PART('hour', started.created_at) date_part,
232+
ROW_NUMBER() OVER (PARTITION BY started.id) rn
233+
FROM sensor_triggers started
234+
JOIN sensor_triggers ended
235+
ON ended.created_at - started.created_at BETWEEN '00:00:00'::time AND '00:00:05'::time
236+
AND started.sensor_id = 2 AND ended.sensor_id = 1
237+
-- Параметр "дата" формирования отчета
238+
AND started.created_at::date = '2019.06.09'
239+
) as temporally
240+
WHERE rn = 1
241+
GROUP BY date_part
242+
),
243+
rows as (
244+
SELECT
245+
hours.hour_literal,
246+
coalesce(incomes.count, 0) income,
247+
coalesce(outcomes.count, 0) outcome
248+
FROM hours
249+
LEFT JOIN outcomes on hours.hour_id = outcomes.date_part
250+
LEFT JOIN incomes on hours.hour_id = incomes.date_part
251+
),
252+
final_row as (
253+
SELECT 'Итог', sum(income), sum(outcome) from rows
254+
)
255+
SELECT * FROM rows
256+
UNION ALL
257+
SELECT * from final_row
258+
```
259+
260+
# Итог
261+
262+
В общем-то на этом и все, в конце используется **UNION ALL** чтобы добавить строку в конец выборки, т.к. **UNION ALL** просто соединяет две выборки в указанном порядке, в отличии от **UNION**, который объединяет выборки и удаляет дубликаты (для поиска дубликатов выборка сортируется).
263+
264+
**Спасибо, что прочитали до конца! Надеюсь, что статья научит чему-нибудь новому :)**

0 commit comments

Comments
 (0)