Comments / Profile of the_unbridled

Александр Максименко @the_unbridled_goose

PostgreSQL Developer / DBA

Profile Publications 17Comments 29Bookmarks 71

the_unbridled_goose May 23 2019 at 18:42

Какое-то ну очень вымученное решение, потерявшее всю легкость, непринужденность и элегантность.

...

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp),
           (5, '2018-06-10 18:46:10'::timestamp, '2018-06-13 10:18:18'::timestamp),
           (6, '2019-04-28 21:00:00'::timestamp, '2019-04-28 21:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2018-12-31'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     subst_days (s_date) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when extract(hours from dd) < 10
                        or extract(hours from dd) >= 19 then interval '0 hours'
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
           and not exists(select 0
                          from holidays
                          where h_date::date = dd::date)
         union all
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                interval '0 hours'
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where not exists(select 1
                          from subst_days
                          where dd::date = s_date::date
                             or extract(dow from dd) between 6 and 7
                             or exists(select 0
                                       from holidays
                                       where h_date::date = dd::date)
             ))
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Look

SQL: задача о рабочем времени

the_unbridled_goose May 22 2019 at 18:39

Ну и дабы уже закрыть гештальт и перестать себя чувствовать как провинившийся студент…

finally

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2018-12-31'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     subst_days (s_date) as (
         values ('2018-04-28'),
                ('2018-06-09'),
                ('2018-12-29')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    when extract(hours from dd) < 10
                        or extract(hours from dd) >= 19 then interval '0 hours'
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where (exists(select 1 from subst_days where dd::date = s_date::date) or extract(dow from dd) between 1 and 5)
           and not exists(select 0
                          from holidays
                          where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Look

SQL: задача о рабочем времени

the_unbridled_goose May 22 2019 at 13:30

В очередной раз понял, нашел и исправил.

n + 1

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                dd,
                first_value(dd) over (partition by id, start_time, stop_time),
                last_value(dd) over (partition by id, start_time, stop_time),
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    when dd = first_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from start_time) between 10 and 18
                        then first_value(dd) over (partition by id, start_time, stop_time) + '1 hour' - start_time
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(date_trunc('hour', start_time), date_trunc('hour', stop_time), '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Look

SQL: задача о рабочем времени

the_unbridled_goose May 21 2019 at 19:20

Вот так вот должно быть хорошо.

Просветление достигнуто

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                to_char(start_time, 'yyyy-mm-dd HH24:MI:SS') as start_time,
                to_char(stop_time, 'yyyy-mm-dd HH24:MI:SS')  as stop_time,
                dd,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 18
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    else interval '1 hour' end               as w_hours
         from periods,
              generate_series(start_time, stop_time, '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Look

SQL: задача о рабочем времени

the_unbridled_goose May 21 2019 at 18:13

Почему недостаточно?) Результат получен, в логике расхождений с истиной особо не вижу, что не так?

Look

SQL: задача о рабочем времени

the_unbridled_goose May 21 2019 at 17:29

Добавил еще шаманства для минут и секунд.

Еще больше загадочности

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                start_time,
                stop_time,
                dd,
                case
                    when dd = last_value(dd) over (partition by id, start_time, stop_time) and
                         extract(hours from stop_time) between 10 and 19
                        then stop_time - last_value(dd) over (partition by id, start_time, stop_time)
                    else interval '1 hour' end as w_hours
         from periods,
              generate_series(start_time, stop_time, '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date)
     )
select id, start_time, stop_time, to_char(sum(w_hours), 'HH24:MI:SS') as work_hrs
from magic
group by id, start_time, stop_time
order by id;

Look

SQL: задача о рабочем времени

the_unbridled_goose May 21 2019 at 14:02

Решение в лоб

with periods(id, start_time, stop_time) as (
    values (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp),
           (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp),
           (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp),
           (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
     holidays (h_date) as (
         values ('2018-01-01'),
                ('2018-01-02'),
                ('2018-01-03'),
                ('2018-01-04'),
                ('2018-01-05'),
                ('2018-01-07'),
                ('2018-01-08'),
                ('2018-02-23'),
                ('2018-03-08'),
                ('2018-05-01'),
                ('2018-05-09'),
                ('2018-06-12'),
                ('2018-11-04'),
                ('2019-01-01'),
                ('2019-01-02'),
                ('2019-01-03'),
                ('2019-01-04'),
                ('2019-01-05'),
                ('2019-01-07'),
                ('2019-01-08'),
                ('2019-02-23'),
                ('2019-03-08'),
                ('2019-05-01'),
                ('2019-05-09'),
                ('2019-06-12'),
                ('2019-11-04')
     ),
     magic as (
         select id,
                start_time,
                stop_time,
                dd
         from periods,
              generate_series(start_time, stop_time - interval '1 hour', '1 hour') dd
         where extract(dow from dd) between 1 and 5
           and extract(hours from dd) >= 10
           and extract(hours from dd) < 19
           and not exists(select 0 from holidays where h_date::date = dd::date))
select id, start_time, stop_time, count(1) as work_hrs
from magic
group by id, start_time, stop_time
order by id
;

Look

И снова о рекурсивных запросах

the_unbridled_goose Jan 10 2017 at 12:12

Неплохо, спасибо за статью.

Look

Релиз DataGrip 2016.3

the_unbridled_goose Nov 26 2016 at 13:43

Прочитал статью и оплатил. Животворящая сила гифок и подробностей о функционале.

Look

О влиянии full-page writes

the_unbridled_goose Nov 25 2016 at 11:10

Попробуйте почитать вот здесь. У меня под рукой имеется только такого рода «общая» информация.

Look

DBGlass — Open-Source кросс-платформенный PostgreSQL GUI клиент

the_unbridled_goose Oct 10 2016 at 11:08

Третий пгадмин вис хотя бы в процессе эксплуатации, ругался и вылетал. Четвертый пошел дальше, если просто оставить его свернутым, а потом развернуть — 7 секунд!!! Я целых 7 секунд могу считать пылинки на белом фоне моего монитора. Это core i7 и 8 гиг RAM.

К слову, тот же DBeaver просто шедевр на фоне пгадминов. Хоть и со своими тараканами.

И главная печаль… Всему опэнсорсу не хватает дебага сиквельного кода из коробки((

Look

Ограничения (сonstraints) PostgreSQL: exclude, частичный unique, отложенные ограничения и др

the_unbridled_goose Oct 4 2016 at 12:12

О. это извечная проблема. Особенно при переводе статей. Назовешь по-русски — теряется смысл, по-английски — выбивается из текста. Получается замкнутый круг из мучений и компромиссов с самим собой.

Look

Новый релиз PostgreSQL 9.6: вклад Postgres Professional

the_unbridled_goose Oct 3 2016 at 14:51

Спасибо Вам большое за Ваш вклад! Новые фичи просто огонь, особенно для полнотекстового поиска.

Look

Как думать на SQL?

the_unbridled_goose Jul 19 2016 at 15:54

Боженьки мои!

Look

PostgreSQL 9.6: Параллелизация последовательного чтения

the_unbridled_goose Jul 15 2016 at 10:44

Ваш вариант воспринимается лучше всего, спасибо, прислушался, поправил везде в тексте.

Look

PostgreSQL 9.6: Параллелизация последовательного чтения

the_unbridled_goose Jul 14 2016 at 17:24

Ох уж эта невнимательность… благодарю, исправлено.

Look

Поиск по Postgres с использованием ZomboDb и elasticsearch

the_unbridled_goose Jun 22 2016 at 19:13

1. Проблема в поиске именно двух подряд идущих слов. Например, Иван Иванов. Из очевидного — искали регуляркой по тексту уже полученных через поиск по вектору данных. Гибкости абсолютно никакой. Необходимо учитывать все вариации окончаний обоих слов, плюс одно дело это формировать руками в базе, и совсем другое — в автоматическом режиме. Да еще и запрос притормаживает ощутимо, так как лайк, или симилар.
2. Использовали GIN.

Look

Пять способов пагинации в Postgres, от базовых до диковинных

the_unbridled_goose May 20 2016 at 19:00

Да, индекса не было нужного.

Было:

(cost=1365076.05..1365161.13 rows=10 width=2501) (actual time=2078.275..2078.763 rows=50 loops=1)

Стало:

(cost=2589.09..2674.17 rows=10 width=2501) (actual time=59.720..60.222 rows=50 loops=1)

И сам explain:

Index Scan (cost=2589.09..2674.17 rows=10 width=2501) (actual time=59.720..60.222 rows=50 loops=1)
    ->  Limit  (cost=1294.04..2587.51 rows=50 width=32) (actual time=23.419..59.575 rows=50 loops=1)
          ->  Index Scan (cost=0.56..11409104.75 rows=441025 width=32) (actual time=0.579..59.549 rows=100 loops=1)
    ->  CTE Scan on temp_rows  (cost=0.00..1.00 rows=50 width=16) (actual time=23.423..59.661 rows=50 loops=1)
Planning time: 0.647 ms
Execution time: 60.287 ms

О — оптимизация)

Look

Пять способов пагинации в Postgres, от базовых до диковинных

the_unbridled_goose May 20 2016 at 17:18

Там вообще все очень непросто, увы. Но спасибо за совет.

Изменения там регулярные, кроме того, долгие хождения по вектору, тяжелому вектору. С вакуумом и статистикой особых проблем нет. Все регулярно, по мере необходимости. Помимо автовакуума, само собой.
Обязательно проверю еще раз что там с индексами.

Look

Пять способов пагинации в Postgres, от базовых до диковинных

the_unbridled_goose May 20 2016 at 13:01

Это проблематично, так как тестировалось на боевом контуре. В таком виде недостаточно?

Index Scan (cost=1310619.54..1310704.63 rows=10 width=2041) (actual time=1906.651..1907.164 rows=50 loops=1)
  CTE temp_rows
    ->  Limit (cost=1310617.85..1310617.97 rows=50 width=32) (actual time=1906.502..1906.517 rows=50 loops=1)
          ->  Sort (cost=1310542.85..1311220.85 rows=271201 width=32) (actual time=1899.475..1904.427 rows=30050 loops=1)
                ->  Bitmap Heap Scan (cost=399032.63..1289016.16 rows=271201 width=32) (actual time=821.209..1723.774 rows=218436 loops=1)
                      ->  Bitmap Index Scan (cost=0.00..398964.83 rows=305711 width=0) (actual time=705.200..705.200 rows=320530 loops=1)
  InitPlan 2 (returns $1)
    ->  CTE Scan on temp_rows  (cost=0.00..1.00 rows=50 width=16) (actual time=1906.506..1906.544 rows=50 loops=1)
Planning time: 0.966 ms
Execution time: 1911.184 ms

Look