Создать гистограмму даты по таблице

Я написал запрос, который возвращает гистограмму в терминале, используя интерфейс командной строки Postgres. Запрос медленный и неэффективный. Я хотел бы изменить это.

В основе у нас есть довольно простой запрос. Мы хотим, чтобы каждая строка была частью общего количества строк в нашей таблице. Предположим, что наше жестко запрограммированное количество строк равно N_ROWS, а наша таблица равна my_table.

Кроме того, допустим, N_ROWS равно 8.

select
    (select count(id) from my_table) / N_ROWS * (N_ROWS - num) as level 
from (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)) as t (num)

В моем случае это возвращает мою ось Y диаграммы как:

Вы уже видите проблемы с этим запросом.

Могу ли я программно сгенерировать ряд строк, используя N_ROWS, а не жестко задавать значение каждой строки в VALUES? Мне также не нравится, как я выполняю новый подсчет по всей таблице для каждой строки, очевидно.

Теперь нам нужна наша ось X, и вот что я придумал:

select
    r.level,
    case
        when (
            select count(id) from my_table where created_at_utc<= '2019-01-01 00:00:00'::timestamp without time zone
        ) >= r.level then true
    end as "2019-01-01"
from (
    select (select count(id) from my_table) / N_ROWS * (N_ROWS - num) as level from (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)) as t (num)
) as r;

Что возвращает наше первое ведро:

 level | 2019-01-01
-------+------------
 71760 |
 62790 |
 53820 |
 44850 |
 35880 |
 26910 | t
 17940 | t
  8970 | t
     0 | t

Я бы не хотел жестко кодировать оператор case для каждого сегмента, но, конечно, я так и сделал. Результаты - это то, что я искал.

 level | 2019-01-01 | 2019-02-01 | 2019-03-01 | 2019-04-01 | 2019-05-01 | 2019-06-01 | 2019-07-01 | 2019-08-01 | 2019-09-01 | 2019-10-01 | 2019-11-01 | 2019-12-01
-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------
 71760 |            |            |            |            |            |            |            |            |            |            |            | t
 62790 |            |            |            |            | t          | t          | t          | t          | t          | t          | t          | t
 53820 |            |            |            | t          | t          | t          | t          | t          | t          | t          | t          | t
 44850 |            |            | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
 35880 |            | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
 26910 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
 17940 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
  8970 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
     0 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t

postgresql date-histogram

WebWanderer 13.11.2019 источник

comment

Для вашего динамического VALUES взгляните на generate_series(). Документы: postgresql.org/docs/current/functions-srf.html - Islingre 13.11.2019

Ответы (1)

arrow_upward
2
arrow_downward

Есть, конечно, несколько улучшений, которые мы можем сделать.

Для начала создадим тестовую таблицу с некоторыми данными:

CREATE TABLE test (id bigint, dt date);
-- Add 1 million rows
insert into test select generate_series(1,100000, 1);
-- Add dates from 2019-01-01 to 2019-01-11
update test set dt='2019-01-01'::date + (id/10000)::int;

Мы можем почти заменить ваш первый запрос для поиска уровней следующим гораздо более быстрым запросом:

   SELECT unnest(percentile_disc(
                  (
                      SELECT array_agg(x) 
                      FROM generate_series(0, 1, (1::numeric)/8) as g(x))
                  ) WITHIN GROUP (ORDER BY id)
                 ) as l
    FROM test;
   l
--------
      1
  12500
  25000
  37500
  50000
  62500
  75000
  87500
 100000
(9 rows)

Обратите внимание, что первый уровень равен 1 вместо 0, но остальные должны быть такими же.

Есть еще несколько трюков, которые мы можем использовать:

Мы получим наш список дат из generate_series
Мы можем сгруппировать тестовые данные по дням (или date_trunc(timestamp), если у вас есть данные с временными метками) и подсчитать идентификаторы. Использование оконной функции над этим числом даст нам совокупную сумму идентификаторов за день.
Мы можем использовать \crosstabview в psql для поворота результирующего запроса.

WITH num_levels AS (
    SELECT 8 as num_levels
), levels as (
   SELECT unnest(percentile_disc(
                  (
                      SELECT array_agg(x) 
                      FROM num_levels
                      CROSS JOIN LATERAL generate_series(0, 1, (1::numeric)/num_levels.num_levels) as g(x))
                  ) WITHIN GROUP (ORDER BY id)
                 ) as l
    FROM test
), dates as (
  SELECT d
  FROM generate_series('2019-01-01T00:00:00'::timestamp, '2019-01-11T00:00:00'::timestamp, '1 day') as g(d)
), counts_per_day AS (
  SELECT dt, 
         sum(counts) OVER (ORDER BY dt) as cum_sum -- the cumulative count
  FROM (
    SELECT dt, 
    count(id) as counts -- The count per day
    FROM test
    GROUP BY dt
  ) sub
)
SELECT l, dt, CASE WHEN cum_sum >= l THEN true ELSE null END
FROM levels, dates
LEFT JOIN counts_per_day ON dt = d
ORDER BY l DESC, d asc
\crosstabview
   l    | 2019-01-01 | 2019-01-02 | 2019-01-03 | 2019-01-04 | 2019-01-05 | 2019-01-06 | 2019-01-07 | 2019-01-08 | 2019-01-09 | 2019-01-10 | 2019-01-11
--------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------
 100000 |            |            |            |            |            |            |            |            |            |            | t
  87500 |            |            |            |            |            |            |            |            | t          | t          | t
  75000 |            |            |            |            |            |            |            | t          | t          | t          | t
  62500 |            |            |            |            |            |            | t          | t          | t          | t          | t
  50000 |            |            |            |            |            | t          | t          | t          | t          | t          | t
  37500 |            |            |            | t          | t          | t          | t          | t          | t          | t          | t
  25000 |            |            | t          | t          | t          | t          | t          | t          | t          | t          | t
  12500 |            | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
      1 | t          | t          | t          | t          | t          | t          | t          | t          | t          | t          | t
(9 rows)

Этот запрос выполнялся за 40 мс на моем ноутбуке.

Даты можно выбрать из максимальных и минимальных дат в тестовой таблице, а интервал можно изменить с 1 дня в зависимости от того, сколько столбцов требуется между максимальным и минимальным.

Jeremy 14.11.2019

comment

Очень впечатляющий SQL более высокого уровня! Благодарю вас! - WebWanderer; 15.11.2019

Создать гистограмму даты по таблице

Ответы (1)

Вопросы по теме