SQL查询以减少行数,使用较少行中的分组值

huangapple go评论59阅读模式
英文:

SQL query to reduce number of rows using grouping values in lesser number of rows

问题

以下是翻译好的部分:

我有一组数据,看起来像下面这样。

SQL查询以减少行数,使用较少行中的分组值

我想要减少其中的行数。如果可能的话,减少到一行将被视为最佳情况。

最终数据集应该是这样的。

SQL查询以减少行数,使用较少行中的分组值

我尝试了SQL中的Min和Max函数。但我不想丢失任何数据。只想填充空白单元格以提高报告的可读性。

如果有人需要示例数据,可以使用以下查询。

select * from
(
SELECT 9999 as primary_key, 1 AS column_1, 2 as column_2, NULL as column_3, NULL as column_4 FROM DUAL
UNION ALL
SELECT 9999, NULL, NULL, 3, 4 FROM DUAL
UNION ALL
SELECT 9999, 5, 6, NULL, NULL FROM DUAL
)
英文:

I have set of data which looks like below

SQL查询以减少行数,使用较少行中的分组值

I want to reduce the rows in it. If possible to reduce up to single row then it would be considered as best case.

Final data set should something like this
SQL查询以减少行数,使用较少行中的分组值

I tried Min and Max functions in SQL. But I don't want to loose any data. Just want to fill blank cells so that readability of report.

If anyone need sample data then you may use below query

select * from
(
SELECT 9999 as primary_key ,1 AS column_1, 2 as column_2, NULL  as column_3, NULL as column_4 FROM DUAL
UNION ALL
SELECT 9999, NULL, NULL, 3, 4 FROM DUAL
UNION ALL
SELECT 9999, 5, 6, NULL, NULL FROM DUAL
)

答案1

得分: 2

我建议将当前数据转换成一个“非标准化状态”,排除空值,并在此过程中添加行号。然后,利用主键和行号将这个临时结果重新转换为所需的最终格式:

WITH UNPIV AS (
    SELECT PRIMARY_KEY, 'column_1' AS COLUMN_NAME, column_1 AS COLUMN_VALUE, ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_1) AS RN FROM your_table WHERE column_1 IS NOT NULL
    UNION ALL
    SELECT PRIMARY_KEY, 'column_2' AS COLUMN_NAME, column_2 AS COLUMN_VALUE, ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_2) AS RN FROM your_table WHERE column_2 IS NOT NULL
    UNION ALL
    SELECT PRIMARY_KEY, 'column_3' AS COLUMN_NAME, column_3 AS COLUMN_VALUE, ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_3) AS RN FROM your_table WHERE column_3 IS NOT NULL
    UNION ALL
    SELECT PRIMARY_KEY, 'column_4' AS COLUMN_NAME, column_4 AS COLUMN_VALUE, ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_4) AS RN FROM your_table WHERE column_4 IS NOT NULL
)
SELECT PRIMARY_KEY, 
       MAX(CASE WHEN COLUMN_NAME = 'column_1' THEN COLUMN_VALUE END) AS COLUMN_1,
       MAX(CASE WHEN COLUMN_NAME = 'column_2' THEN COLUMN_VALUE END) AS COLUMN_2,
       MAX(CASE WHEN COLUMN_NAME = 'column_3' THEN COLUMN_VALUE END) AS COLUMN_3,
       MAX(CASE WHEN COLUMN_NAME = 'column_4' THEN COLUMN_VALUE END) AS COLUMN_4
FROM UNPIV
GROUP BY PRIMARY_KEY, RN
ORDER BY PRIMARY_KEY, RN;
英文:

I suggest working the current data into an "unpivoted state" that excludes NULL values and whilst doing this add a row number into the mix. Then once that is available then re-pivot that interim result into the wanted final format using both the primary_key and the row number:

WITH UNPIV AS (
    SELECT PRIMARY_KEY, 'column_1' AS COLUMN_NAME, column_1 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_1) AS RN
    FROM your_table
    WHERE column_1 IS NOT NULL
    
    UNION ALL
    
    SELECT PRIMARY_KEY, 'column_2' AS COLUMN_NAME, column_2 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_2) AS RN
    FROM your_table
    WHERE column_2 IS NOT NULL
    
    UNION ALL
    
    SELECT PRIMARY_KEY, 'column_3' AS COLUMN_NAME, column_3 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_3) AS RN
    FROM your_table
    WHERE column_3 IS NOT NULL
    
    UNION ALL
    
    SELECT PRIMARY_KEY, 'column_4' AS COLUMN_NAME, column_4 AS COLUMN_VALUE
           , ROW_NUMBER() OVER(PARTITION BY PRIMARY_KEY ORDER BY column_4) AS RN
    FROM your_table
    WHERE column_4 IS NOT NULL
    )
SELECT PRIMARY_KEY, 
       MAX(CASE WHEN COLUMN_NAME = 'column_1' THEN COLUMN_VALUE END) AS COLUMN_1,
       MAX(CASE WHEN COLUMN_NAME = 'column_2' THEN COLUMN_VALUE END) AS COLUMN_2,
       MAX(CASE WHEN COLUMN_NAME = 'column_3' THEN COLUMN_VALUE END) AS COLUMN_3,
       MAX(CASE WHEN COLUMN_NAME = 'column_4' THEN COLUMN_VALUE END) AS COLUMN_4
FROM UNPIV
GROUP BY PRIMARY_KEY, RN
ORDER BY PRIMARY_KEY, RN
PRIMARY_KEY COLUMN_1 COLUMN_2 COLUMN_3 COLUMN_4
9999 1 2 3 4
9999 5 6 null null

fiddle

Note the arrangement of values into these rows is determined by the row_number() calculation. So there is no guarantee that values will re-align into the same rows that they originated from.

huangapple
  • 本文由 发表于 2023年7月5日 01:32:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76614810.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定