Выбор суммы и текущего баланса за последние 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 недели тестирования обнаружил проблему. Присоединение левой части к серии, а затем добавление коалесцирования для отображения нулей, а не нулей для пустых месяцев, вызывает проблему с текущим балансом.

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

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;

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

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

Источник

1 ответ

Решение

Основное решение

Создайте полный список месяцев и 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 с минимальным тестовым набором.

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

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

  • Так как вы выбираете один 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 на ежегодной основе или что-то.

Источник
Другие вопросы по тегам