Выбор суммы и текущего баланса за последние 18 месяцев с помощью generate_series

У меня есть этот рабочий запрос, но мне нужно добавить все месяцы к моему результату, независимо от того, были ли проданы товары в течение этого месяца:

select * from (
select
to_char(max(change_date), 'YYYY-MON')::varchar(8) as yyyymmm,
max(change_date) as yearmonth,
sum(vic.sold_qty / item_size.qty)::numeric(18,2) as sold_qty,  -- sold monthly
sum(sum(on_hand)) OVER (PARTITION BY vic.item_id order by year,month) as on_hand --running balance
from (((view_item_change vic
left join item on vic.item_id = item.item_id)
left join item_size on item_size.item_id = vic.item_id and item_size.name = item.sell_size)
left join item_plu on vic.item_id = item_plu.item_id and item_plu.seq_num = 0)
where 1 = 1   -- cannot limit date here as its used to show running balance.
and vic.change_date < current_date - date_part('day',current_date)::integer --show only till end of last month
and item.item_id = (select item_id from item_plu where number = '51515')
group by vic.item_id, year, month
) as t 
where yearmonth > current_date - date_part('day',current_date)::integer - 540 -- 18 months

что дает мне что-то вроде этого:

"2013-JUN";"2013-06-29";0.00;7.0000
"2013-JUL";"2013-07-22";0.00;6.0000
"2013-AUG";"2013-08-28";2.00;4.0000
"2013-SEP";"2013-09-02";0.00;4.0000
"2013-OCT";"2013-10-28";0.00;4.0000
"2013-NOV";"2013-11-15";0.00;4.0000
"2013-DEC";"2013-12-16";0.00;6.0000
"2014-FEB";"2014-02-10";1.00;5.0000
"2014-APR";"2014-04-09";0.00;5.0000

Но я также хочу показать месяцы 2014-JAN и 2014-MAR, чтобы моя диаграмма была лучше масштабирована по времени.

Я знаю, как сделать generate_series(start_date, end_date, '1 month') интервалов, но я не совсем понимаю, как я могу соединить эту серию с набором результатов выше.

Я принял первый ответ, но после 2 недель тестирования обнаружил проблему. Левое соединение с рядом, а затем добавление объединения, чтобы показать 0, а не нули для пустых месяцев, вызывает проблему с текущим балансом.

Результирующий запрос:

SELECT yyyymmm, yyyymmm, 
coalesce(sold_qty,0) sold_qty, coalesce(on_hand,0) on_hand
FROM  (
   SELECT date_trunc('month', month_series)::date as yyyymmm
   FROM   generate_series(current_date - date_part('day',current_date)::integer - 540  
   ,current_date- date_part('day',current_date)::integer
   , interval '1 month') month_series
   ) month_series
LEFT  JOIN (
    select * from (
        select
        date_trunc('month', max(change_date))::date as yyyymmm,
        max(change_date) as yearmonth,
        sum(vic.sold_qty / item_size.qty)::numeric(18,2) as sold_qty,
        sum(sum(on_hand)) OVER (PARTITION BY vic.item_id order by year,month) as on_hand
        from (((view_item_change vic
        left join item on vic.item_id = item.item_id)
        left join item_size on item_size.item_id = vic.item_id and item_size.name = item.sell_size)
        left join item_plu on vic.item_id = item_plu.item_id and item_plu.seq_num = 0)
        where 1 = 1   -- cannot limit date here as its used to show running balance.
        --vic.change_date >= current_date - date_part('day',current_date)::integer - 730  -- only get results for last 
        --show only till end of last month
        and vic.change_date <= current_date - date_part('day',current_date)::integer
        and item.item_id = (select item_id from item_plu where number = '19M7077')
        group by vic.item_id, year, month
    ) as a 
    where yyyymmm > current_date - date_part('day',current_date)::integer - 540 -- 18 months
) q USING (yyyymmm)
order by 1

Результаты, которые я получаю:

"2013-07-01";"2013-07-01";0;0
"2013-08-01";"2013-08-01";0;0
"2013-09-01";"2013-09-01";1.00;53.0000
"2013-10-01";"2013-10-01";0;0
"2013-11-01";"2013-11-01";0;0
"2013-12-01";"2013-12-01";0.00;53.0000
"2014-01-01";"2014-01-01";0.00;52.0000
"2014-02-01";"2014-02-01";0;0
"2014-03-01";"2014-03-01";0;0
"2014-04-01";"2014-04-01";0;0

Но я хочу:

"2013-07-01";"2013-07-01";0;53.0000
"2013-08-01";"2013-08-01";0;53.0000
"2013-09-01";"2013-09-01";1.00;53.0000
"2013-10-01";"2013-10-01";0;53.0000
"2013-11-01";"2013-11-01";0;0;53.0000
"2013-12-01";"2013-12-01";0.00;53.0000
"2014-01-01";"2014-01-01";0.00;52.0000
"2014-02-01";"2014-02-01";0;0;52.0000
"2014-03-01";"2014-03-01";0;0;52.0000
"2014-04-01";"2014-04-01";0;0;52.0000

Определения таблиц

CREATE TABLE item
(
  item_id character(22) NOT NULL,
  version integer NOT NULL,
  created_by character varying(16) NOT NULL,
  updated_by character varying(16),
  inactive_by character varying(16),
  created_on date NOT NULL,
  updated_on date,
  inactive_on date,
  external_id numeric(14,0),
  description character varying(40) NOT NULL,
  dept_id character(22),
  subdept_id character(22),
  sell_size character varying(8) NOT NULL,
  purch_size character varying(8) NOT NULL
);
CREATE TABLE item_change
(
  item_id character(22) NOT NULL,
  size_name character varying(8) NOT NULL,
  store_id character(22) NOT NULL,
  change_date date NOT NULL,
  on_hand numeric(18,4) NOT NULL,   -- sum column / item_id = total on_hand
  total_cost numeric(18,4) NOT NULL,
  on_order numeric(18,4) NOT NULL,
  sold_qty numeric(18,4) NOT NULL,
  sold_cost numeric(18,4) NOT NULL,
  sold_price numeric(18,4) NOT NULL,
  recv_qty numeric(18,4) NOT NULL,
  recv_cost numeric(18,4) NOT NULL,
  adj_qty numeric(18,4) NOT NULL,
  adj_cost numeric(18,4) NOT NULL
);

CREATE TABLE item_size
(
  item_id character(22) NOT NULL,
  seq_num integer NOT NULL,
  name character varying(8) NOT NULL,
  qty numeric(18,4) NOT NULL,
  weight numeric(18,4) NOT NULL,
  CONSTRAINT item_size_pkey PRIMARY KEY (item_id, seq_num),
  CONSTRAINT item_size_c0 FOREIGN KEY (item_id)
      REFERENCES item (item_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT item_size_c1 UNIQUE (item_id, name)
);

CREATE TABLE item_plu
(
  item_id character(22) NOT NULL,
  seq_num integer NOT NULL,
  "number" character varying(18) NOT NULL,
  size_name character varying(8)
);


CREATE OR REPLACE VIEW view_item_change AS 
 SELECT date_part('year'::text, item_change.change_date) AS year,
date_part('month'::text, item_change.change_date) AS month,
date_part('week'::text, item_change.change_date) AS week,
date_part('quarter'::text, item_change.change_date) AS quarter,
date_part('dow'::text, item_change.change_date) AS dow,
item_change.item_id,
item_change.size_name,
item_change.store_id,
item_change.change_date,
item_change.on_hand,
item_change.total_cost,
item_change.on_order,
item_change.sold_qty,
item_change.sold_cost,
item_change.sold_price,
item_change.recv_qty,
item_change.recv_cost,
item_change.adj_qty,
item_change.adj_cost
FROM item_change;

В месяц есть только одна строка, потому что я также делаю группу по годам, месяцам. Как видите, в представлении есть столбцы года, квартала, месяца, недели, индекса индекса индекса для упрощения составления отчетов.

Чтобы получить рабочие данные для этого, если требуется, я могу это сделать, но это потребует ручного создания. Я упростил таблицы и исключил все ограничения и некоторые столбцы.


postgresql sql window-functions generate-series running-balance
person markdueck    schedule 27.12.2014    source источник
comment
Вы GROUP BY item_id, но тогда в результате не item_id и только одна строка в месяц? Это странно. Кроме того, year, date и change_date не подходят для таблицы, что делает эту игру угадайкой. Было бы намного проще решить с фактическими определениями таблиц и примерами данных. По крайней мере, уточните таблицы всех столбцов в запросе, чтобы дать нам шанс на победу.   -  person Erwin Brandstetter    schedule 09.01.2015
comment
Я не вижу определения для item_size? Ваша версия Postgres?   -  person Erwin Brandstetter    schedule 09.01.2015
comment
добавленный выше item_size. Постгрес версии 9.3.   -  person markdueck    schedule 09.01.2015
comment
Либо мы получим несколько элементов в результате: тогда вы должны включить item_id в результат. Или мы получаем данные только для одного элемента: тогда вы можете еще больше упростить запрос.   -  person Erwin Brandstetter    schedule 09.01.2015
comment
Все, что мне нужно, это результат одного элемента. Это текущее время выполнения запроса не соответствует тому, что я могу использовать. Обратите внимание, что таблица item_change содержит 5,4 млн записей. Предыдущий запрос выполняется за 32 мс, а этот занимает более 80 секунд и все еще не завершен.   -  person markdueck    schedule 09.01.2015
comment
Это явный признак того, что что-то идет не так. Добавление большего количества строк будет стоить немного, но не такого много.   -  person Erwin Brandstetter    schedule 09.01.2015

Ответы (1)


arrow_upward
4
arrow_downward

Базовое решение

Сгенерируйте полный список месяцев и LEFT JOIN остальные к нему:

SELECT *
FROM  (
   SELECT to_char(m, 'YYYY-MON') AS yyyymmm
   FROM   generate_series(<start_date>, <end_date>, interval '1 month') m
   ) m
LEFT  JOIN ( <your query here> ) q USING (yyyymmm);

Связанные ответы с дополнительным объяснением:

Расширенное решение для вашего случая

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

Я добиваюсь этого сейчас с LEFT JOIN LATERAL.

SELECT COALESCE(m.yearmonth, c.yearmonth)::date, sold_qty, on_hand
FROM  (
   SELECT yearmonth
        , COALESCE(sold_qty, 0) AS sold_qty
        , sum(on_hand_mon) OVER (ORDER BY yearmonth) AS on_hand
        , lead(yearmonth)  OVER (ORDER BY yearmonth)
                                - interval '1 month' AS nextmonth
   FROM (
      SELECT date_trunc('month', c.change_date) AS yearmonth
           , sum(c.sold_qty / s.qty)::numeric(18,2) AS sold_qty
           , sum(c.on_hand) AS on_hand_mon
      FROM   item_change      c         
      LEFT   JOIN item        i USING (item_id)
      LEFT   JOIN item_size   s ON s.item_id = i.item_id AND s.name = i.sell_size
      LEFT   JOIN item_plu    p ON p.item_id = i.item_id AND p.seq_num = 0
      WHERE  c.change_date < date_trunc('month', now()) - interval '1 day'
      AND    c.item_id = (SELECT item_id FROM item_plu WHERE number = '51515')
      GROUP  BY 1
      ) sub
   ) c
LEFT   JOIN LATERAL generate_series(c.yearmonth
                                  , c.nextmonth
                                  , interval '1 month') m(yearmonth) ON TRUE
WHERE  c.yearmonth > date_trunc('year', now()) - interval '540 days'
ORDER  BY COALESCE(m.yearmonth, c.yearmonth);

SQL Fiddle с минимальным набором тестов.

Основные моменты:

  • Я полностью удалил ваш VIEW из запроса. Много затрат без выгоды.

  • Поскольку вы выбрали один item_id, вам не нужно GROUP BY item_id или PARTITION BY item_id.

  • Используйте короткие псевдонимы таблиц и делайте все ссылки однозначными, особенно при размещении сообщений на общедоступном форуме.

  • Скобки в ваших соединениях были просто шумом. По умолчанию соединения в любом случае выполняются слева направо.

  • Упрощенные границы даты (поскольку я работаю с временными метками):

    date_trunc('year', current_date)  - interval '540 days'
date_trunc('month', current_date) - interval '1 day'

    эквивалентно, но проще и быстрее, чем:

    current_date - date_part('day',current_date)::integer - 540
current_date - date_part('day',current_date)::integer

  • Теперь я заполняю пропущенные месяцы после всех расчетов generate_series() вызовами на строку.

  • Это должно быть LEFT JOIN LATERAL ... ON TRUE, а не краткая форма JOIN LATERAL, чтобы поймать угловой регистр последней строки. Детальное объяснение:

Важные примечания:

character(22) — это ужасный тип данных для первичного ключа (или любого столбца). Подробности:

В идеале это должен быть столбец int или bigint или, возможно, UUID.

Кроме того, хранение денежных сумм в виде money или integer (представляющий центы) в целом работает намного лучше.

В долгосрочной перспективе производительность неизбежно ухудшится, поскольку вам придется включать в расчет все строки с самого начала. Вы должны отрезать старые строки и материализовать баланс on_hold на ежегодной основе или что-то в этом роде.

person Erwin Brandstetter    schedule 27.12.2014
comment
Я принял ответ на прошлой неделе, но теперь я узнаю, что он неверен. Текущий баланс должен сохранить то, что было в предыдущей записи, если пропущен месяц. С левым соединением, а затем добавленным объединением к суммам, я получаю 0 для текущего баланса, но это неверно. Любые предложения, или я должен переписать запрос, используя generate_series в качестве базы? - person markdueck; 09.01.2015
comment
@markdueck: я добавил расширенное решение. В основном переписал ваш запрос, чтобы распутать его. - person Erwin Brandstetter; 09.01.2015
comment
- Большое спасибо за вашу помощь, но, как упоминалось выше в предыдущем комментарии, время для этого запроса занимает слишком много времени, и он не возвращается с правильным начальным текущим балансом - это означает, что он запускает текущий баланс с даты начала, не включая сумму всех предыдущих on_hand. Я думал о переходе на что-то вроде этого: select (select sum(sold_qty) from item_change ...) as sold_qty, (select sum(on_hand) from item_change where clause .... ) as OH from (series ) -- где предложение будет включать даты из серии. - person markdueck; 09.01.2015
comment
@markdueck: я был обманут, думая, что вы действительно хотите вернуть несколько item_id вашим GROUP BY item_id. Кроме того, у меня была ошибка: подвыборка не может быть объединена с условием соединения таким образом. Добавлена ​​значительно упрощенная версия с объяснением обновлений и скрипкой. - person Erwin Brandstetter; 09.01.2015
comment
Привет еще раз. к сожалению, приведенный выше запрос не работает, потому что дата ограничена слишком рано, поэтому текущий баланс все еще неверен. Я скорректировал рабочий запрос и поместил его в другой выбор, из которого я ограничил даты здесь. Я не уверен. если я должен изменить свой исходный пост на и включить правильный ответ, или вы хотите это сделать? Спасибо Спасибо. ваша помощь очень ценится!! - person markdueck; 09.01.2015
comment
что касается изменения характера (22) - к сожалению, дизайн базы данных разработан не мной, и я не могу его изменить. Это для системы учета под названием Quasar от Linux Canada Inc.. - person markdueck; 09.01.2015
comment
@markdueck: character varying(22) уже намного лучше, чем character(22). И: очень хитрый запрос! Я добавил еще одно решение и надеюсь, что это решит его навсегда. Также обновлена ​​скрипка. - person Erwin Brandstetter; 10.01.2015