在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屋！