我有这个工作查询，但我需要将所有月份添加到我的结果中，无论该月售出的商品是否：
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;
每个月只有一行，因为我也按年、月做一个组。 如您所见，该视图具有年、季度、月、周、道琼斯列，以便于报告。
如果需要，要为此获取工作数据，我可以这样做，但这需要手动创建它。我简化了表格，省略了所有约束和一些列。
基本解决方案
生成月份的完整列表，并将其余部分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);
相关答案及更多解释：
- 在 postgres 中加入对generate_series的计数查询，并将空值检索为"0"
- 在 Rails+Postgres 中按任意时间间隔对记录进行计数的最佳方式
适合您案例的先进解决方案
您的查询比我最初理解的要复杂。您需要所选项目的所有行的运行总和，然后要修剪早于最小日期的行，并使用上个月预先计算的总和填充缺失的月份。
我现在用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 摆弄一个最小的测试用例。
要点：
我从查询中完全删除了您的视图。付出很多代价却没有收获。
由于您选择了单个
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)是主键（或任何列）的糟糕数据类型。详：
- 使用数据类型"text"存储字符串有什么缺点吗？
理想情况下，这将是一个int或bigint列，或者可能是一个UUID。
此外，将货币金额存储为money类型或integer（代表美分）整体表现要好得多。
从长远来看，性能必然会下降，因为您必须从一开始就在计算中包括所有行。你应该切断旧行，每年实现on_hold的余额或其他东西。