英文:
SQL query to reduce number of rows using grouping values in lesser number of rows
问题
以下是翻译好的部分:
我有一组数据,看起来像下面这样。
我想要减少其中的行数。如果可能的话,减少到一行将被视为最佳情况。
最终数据集应该是这样的。
我尝试了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
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
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 |
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论