在Redshift中拆分行 [英] splitting rows in Redshift
问题描述
在我的表中,数据如下:
In my table the data is as below:
col1 col2 col3 col4
A1 A2 A3 4
B1 B2 B3 3
C1 C2 C3 1
我需要以下输出:
col1 col2 col3 col4
A1 A2 A3 1
A1 A2 A3 2
A1 A2 A3 3
A1 A2 A3 4
B1 B2 B3 1
B1 B2 B3 2
B1 B2 B3 3
C1 C2 C3 1
我使用Redshift DB。
Im using Redshift DB.
推荐答案
您是正确的,Redshift当前不支持generate_series。解决此问题的一种方法是生成自己的系列表并将其加入。在下面的示例中,我只是对pg_attribute表做了row_number()来生成序列。您可以调整TOP(v)值来调整序列中现在想要的许多数字,如果您需要的pg_attribute超出了限制,请尝试将pg_attribute与自身交叉连接。我并不是说这是生成序列表的最佳方法,您可以根据需要生成它。我的主要观点是,您将需要用它来代替generate_series。
You're correct, Redshift currently doesn't support generate_series. One way to get around this is to generate your own series table and join to that. In my example below I just did a row_number() against the pg_attribute table to generate the sequence. You can adjust TOP (v) value to adjust now many numbers you want in your sequence, if you need more than what pg_attribute can give you, try cross joining pg_attribute with itself. I don't claim this to be the best way to generate a sequence table, you can generate it any way you want; my main point is that you'll need one to substitute for generate_series.
一旦有了序列表,则只需简单的联接即可获得结果。
完整示例:
Once you have your series table, then its a simple join to get your result. Complete Example:
-- Setup Example
CREATE TABLE test
(
col1 char(2),
col2 char(2),
col3 char(2),
col4 integer
);
INSERT INTO test(col1, col2, col3, col4)
VALUES
('A1', 'A2', 'A3', 4),
('B1', 'B2', 'B3', 3),
('C1', 'C2', 'C3', 1);
-- Generate 10 sequence numbers to table. Adjust as needed or roll your own
SELECT TOP 10 ROW_NUMBER() OVER (PARTITION BY attnum ORDER BY attnum) n
INTO sequence
FROM pg_catalog.pg_attribute;
-- Example Query
SELECT col1, col2, col3, s.n
FROM test t
INNER JOIN sequence s ON s.n <= t.col4
ORDER BY col1, col2, col3, s.n;
-- Clean up
DROP TABLE sequence;
DROP TABLE test;
这篇关于在Redshift中拆分行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!