这是数据库布局。我有一张桌子,每天随时间推移销售稀少。如果某物品在2015年1月1日有10笔销售,我将有一个条目,但是如果我有0个,则我没有条目。这样的事情。
|--------------------------------------|
| day_of_year | year | sales | item_id |
|--------------------------------------|
| 01 | 2015 | 20 | A1 |
| 01 | 2015 | 11 | A2 |
| 07 | 2015 | 09 | A1 |
| ... | ... | ... | ... |
|--------------------------------------|
这就是我获得1个项目的时间序列的方式。
SELECT doy, max(sales) FROM (
SELECT day_of_year AS doy,
sales AS sales
FROM myschema.entry_daily
WHERE item_id = theNameOfmyItem
AND year = 2015
AND day_of_year < 150
UNION
SELECT doy AS doy,
0 AS sales
FROM generate_series(1, 149) AS doy) as t
GROUP BY doy
ORDER BY doy;
而且我目前循环使用R对每个项目进行1个查询。然后,我将结果汇总到一个数据框中。但这很慢。我实际上只希望有一个查询,它将以以下形式聚合所有数据。
|----------------------------------------------|
| item_id | 01 | 02 | 03 | 04 | 05 | ... | 149 |
|----------------------------------------------|
| A1 | 10 | 00 | 00 | 05 | 12 | ... | 11 |
| A2 | 11 | 00 | 30 | 01 | 15 | ... | 09 |
| A3 | 20 | 00 | 00 | 05 | 17 | ... | 20 |
| ... |
|----------------------------------------------|
这可能吗?顺便说一下,我正在使用Postgres数据库。
获得预期结果的最简单,最快的方法。sales
在客户端程序中解析列很容易。
select item, string_agg(coalesce(sales, 0)::text, ',') sales
from (
select distinct item_id item, doy
from generate_series (1, 10) doy -- change 10 to given n
cross join entry_daily
) sub
left join entry_daily on item_id = item and day_of_year = doy
group by 1
order by 1;
item | sales
------+----------------------
A1 | 20,0,0,0,0,0,9,0,0,0
A2 | 11,0,0,0,0,0,0,0,0,0
(2 rows)
根据解决方案1使用array_agg()
代替string_agg()
。该函数创建具有给定列数的视图。
create or replace function create_items_view(view_name text, days int)
returns void language plpgsql as $$
declare
list text;
begin
select string_agg(format('s[%s] "%s"', i::text, i::text), ',')
into list
from generate_series(1, days) i;
execute(format($f$
drop view if exists %s;
create view %s as select item, %s
from (
select item, array_agg(coalesce(sales, 0)) s
from (
select distinct item_id item, doy
from generate_series (1, %s) doy
cross join entry_daily
) sub
left join entry_daily on item_id = item and day_of_year = doy
group by 1
order by 1
) q
$f$, view_name, view_name, list, days)
);
end $$;
用法:
select create_items_view('items_view_10', 10);
select * from items_view_10;
item | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10
------+----+---+---+---+---+---+---+---+---+----
A1 | 20 | 0 | 0 | 0 | 0 | 0 | 9 | 0 | 0 | 0
A2 | 11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(2 rows)
易于使用,但由于需要定义行格式,因此对于更多的列数非常不舒服。
create extension if not exists tablefunc;
select * from crosstab (
'select item_id, day_of_year, sales
from entry_daily
order by 1',
'select i from generate_series (1, 10) i'
) as ct
(item_id text, "1" int, "2" int, "3" int, "4" int, "5" int, "6" int, "7" int, "8" int, "9" int, "10" int);
item_id | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10
---------+----+---+---+---+---+---+---+---+---+----
A1 | 20 | | | | | | 9 | | |
A2 | 11 | | | | | | | | |
(2 rows)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句