如何在一个sql查询中生成多个时间序列? [英] How to generate multiple time series in one sql query?
问题描述
这是数据库布局.我有一张表，随着时间的推移，销售额很少，每天汇总.如果我在 01-01-2015 有 10 个销售的项目，我将有一个条目，但如果我有 0，那么我没有条目.像这样的东西.
Here is the database layout. I have a table with sparse sales over time, aggregated per day. If for an item I have 10 sales on the 01-01-2015, I will have an entry, but If I have 0, then I have no entry. Something like this.
|--------------------------------------|
| day_of_year | year | sales | item_id |
|--------------------------------------|
| 01 | 2015 | 20 | A1 |
| 01 | 2015 | 11 | A2 |
| 07 | 2015 | 09 | A1 |
| ... | ... | ... | ... |
|--------------------------------------|
这就是我如何获得 1 个项目的时间序列.
This is how I get a time series for 1 item.
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 次查询.然后我将结果汇总到一个数据框中.但这非常缓慢.我实际上只希望有一个查询来聚合以下形式的所有数据.
And I currently loop with R making 1 query for every item. I then aggregate the results in a dataframe. But this is very slow. I would actually like to have only one query that would aggregate all the data in the following form.
|----------------------------------------------|
| 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 数据库.
Would this be possible? By the way I am using a Postgres database.
推荐答案
解决方案 1. 使用聚合的简单查询.
获得预期结果的最简单、最快的方法.解析客户端程序中的 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)
解决方案 2. 动态创建的视图.
基于解决方案 1，使用 array_agg() 而不是 string_agg().该函数创建一个具有给定列数的视图.
Solution 2. Dynamically created view.
Based on the solution 1 with array_agg() instead of string_agg(). The function creates a view with a given number of columns.
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)
解决方案 3. 交叉表.
易于使用，但由于需要定义行格式而对更多的列感到非常不舒服.
Solution 3. Crosstab.
Easy to use, but very uncomfortable with the greater number of columns due to the need to define the row format.
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)
这篇关于如何在一个sql查询中生成多个时间序列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
