英文:
SQL - Create multiple columns for fill rates based on conditions, with GROUP BY
问题
以下是翻译好的代码部分：
-- 创建临时表
CREATE TABLE #TempTable (
ID varchar(10),
GroupCD varchar(10),
SurrogateKEY1 varchar(10),
SurrogateKEY2 varchar(10)
)
-- 插入示例数据
INSERT INTO #TempTable (ID, GroupCD, SurrogateKEY1, SurrogateKEY2)
VALUES
('1', 'UNK', '12345', '89225'),
('3', 'ABC', NULL, '44658'),
('3', 'DEF', NULL, '99658'),
('5', 'ABC', '09184', NULL),
('4', 'DEF', NULL, '85598'),
('1', 'GHI', '80642', '77890')
-- 计算SurrogateKey列的填充率，按ID分组
SELECT
ID,
CAST(SUM(CASE WHEN SurrogateKEY1 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR,
CAST(SUM(CASE WHEN SurrogateKEY2 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR
FROM #TempTable
GROUP BY ID
-- 扩展查询以基于GroupCD = 'ABC' 或其他代码计算填充率
SELECT
ID,
CAST(SUM(CASE WHEN GroupCD <> 'ABC' THEN CASE WHEN SurrogateKEY1 IS NULL THEN 0 ELSE 1 END ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_NonABC,
CAST(SUM(CASE WHEN GroupCD = 'ABC' THEN CASE WHEN SurrogateKEY1 IS NULL THEN 0 ELSE 1 END ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_ClassFilterABC,
CAST(SUM(CASE WHEN GroupCD <> 'ABC' THEN CASE WHEN SurrogateKEY2 IS NULL THEN 0 ELSE 1 END ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_NonABC,
CAST(SUM(CASE WHEN GroupCD = 'ABC' THEN CASE WHEN SurrogateKEY2 IS NULL THEN 0 ELSE 1 END ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_ClassFilterABC
FROM #TempTable
GROUP BY ID
希望这些翻译对你有所帮助。
英文:
Some example data:
CREATE TABLE #TempTable (
ID varchar(10),
GroupCD varchar(10),
SurrogateKEY1 varchar(10),
SurrogateKEY2 varchar(10)
)
INSERT INTO #TempTable (ID, GroupCD, SurrogateKEY1, SurrogateKEY2)
VALUES
('1', 'UNK', '12345', '89225'),
('3', 'ABC', NULL, '44658'),
('3', 'DEF', NULL, '99658'),
('5', 'ABC', '09184', NULL),
('4', 'DEF', NULL, '85598'),
('1', 'GHI', '80642', '77890')
| ID | GroupCD | SurrogateKEY1 | SurrogateKEY2 |
|---|---|---|---|
| 1 | UNK | 12345 | 89225 |
| 3 | ABC | NULL | 44658 |
| 3 | DEF | NULL | 99658 |
| 5 | ABC | 09184 | NULL |
| 4 | DEF | NULL | 85598 |
| 1 | GHI | 80642 | 77890 |
I would like to calculate the fill rates for the SurrogateKey columns, grouped by the IDs, which I have code for:
SELECT
ID,
CAST(SUM(CASE WHEN SurrogateKEY1 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR,
CAST(SUM(CASE WHEN SurrogateKEY2 IS NULL THEN 0 ELSE 1 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR
FROM #TempTable
GROUP BY ID
| ID | SurrogateKEY1_fR | SurrogateKEY2_fR |
|---|---|---|
| 1 | 100.000 | 100.000 |
| 3 | 0.000 | 100.000 |
| 4 | 0.000 | 100.000 |
| 5 | 100.000 | 0.000 |
I'd like to expand this query so the fill rate calculation will be based on the GroupCD = 'ABC' or every other code.
I would like the output to be:
| ID | SurrogateKEY1_fR_NonABC | SurrogateKEY1_fR_ClassFilterABC | SurrogateKEY2_fR_NonABC | SurrogateKEY2_fR_ClassFilterABC |
|---|---|---|---|---|
| 1 | 100.000 | 0.000 | 100.000 | 0.000 |
| 3 | 0.000 | 0.000 | 100.000 | 100.000 |
| 4 | 0.000 | 0.000 | 100.000 | 0.000 |
| 5 | 0.000 | 100.000 | 0.000 | 0.000 |
Does anybody know how to leverage CASE statements or another avenue in order to produce this output?
答案1
得分: 2
以下是翻译好的代码部分：
SELECT
ID,
CAST(SUM(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_NonABC,
CAST(SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_ClassFilterABC,
CAST(SUM(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_NonABC,
CAST(SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_ClassFilterABC
FROM #TempTable
GROUP BY ID
请注意，上述代码是SQL查询的一部分，用于计算不同条件下的百分比。
英文:
Its can be done this way :
SELECT
ID,
CAST(SUM(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_NonABC,
CAST(SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY1_fR_ClassFilterABC,
CAST(SUM(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_NonABC,
CAST(SUM(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1 ELSE 0 END) / CAST(COUNT(1) AS FLOAT) * 100 AS Decimal(8,3)) AS SurrogateKEY2_fR_ClassFilterABC
FROM #TempTable
GROUP BY ID
The result is a little bit different that you expected :
答案2
得分: 1
;WITH CTE AS
(
SELECT ID,
GrpKey = CASE WHEN GroupCD = 'ABC' THEN GroupCD ELSE 'NonABC' END,
SurrogateKEY1fr = CASE WHEN SurrogateKEY1 IS NULL THEN 0.0 ELSE 1.0 END,
SurrogateKEY2fr = CASE WHEN SurrogateKEY2 IS NULL THEN 0.0 ELSE 1.0 END
FROM TempTable
),
P1 AS
(
SELECT ID, [ABC] AS ABCKEY1fr, [NonABC] AS NonABCKEY1fr
FROM CTE AS DT
PIVOT(MAX(SurrogateKEY1fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT
),
P2 AS
(
SELECT ID, [ABC] AS ABCKEY2fr, [NonABC] AS NonABCKEY2fr
FROM CTE AS DT
PIVOT(MAX(SurrogateKEY2fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT
)
SELECT P1.ID, COALESCE(P1.ABCKEY1fr, 0.0) * 100.0 AS ABCKEY1fr,
COALESCE(P1.NonABCKEY1fr, 0.0) * 100.0 AS NonABCKEY1fr,
COALESCE(P2.ABCKEY2fr, 0.0) * 100.0 AS ABCKEY2fr,
COALESCE(P2.NonABCKEY2fr, 0.0) * 100.0 AS NonABCKEY2fr
FROM P1
INNER JOIN P2
ON P2.ID = P1.ID;
英文:
I'd suggest to use CTE and PIVOT.
;WITH CTE AS
(
SELECT ID,
GrpKey = CASE WHEN GroupCD = 'ABC' THEN GroupCD ELSE 'NonABC' END,
SurrogateKEY1fr = CASE WHEN SurrogateKEY1 IS NULL THEN 0.0 ELSE 1.0 END,
SurrogateKEY2fr = CASE WHEN SurrogateKEY2 IS NULL THEN 0.0 ELSE 1.0 END
FROM TempTable
),
P1 AS
(
SELECT ID, [ABC] AS ABCKEY1fr, [NonABC] AS NonABCKEY1fr
FROM CTE AS DT
PIVOT(MAX(SurrogateKEY1fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT
),
P2 AS
(
SELECT ID, [ABC] AS ABCKEY2fr, [NonABC] AS NonABCKEY2fr
FROM CTE AS DT
PIVOT(MAX(SurrogateKEY2fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT
)
SELECT P1.ID, COALESCE(P1.ABCKEY1fr, 0.0) * 100.0 AS ABCKEY1fr,
COALESCE(P1.NonABCKEY1fr, 0.0) * 100.0 AS NonABCKEY1fr,
COALESCE(P2.ABCKEY2fr, 0.0) * 100.0 AS ABCKEY2fr,
COALESCE(P2.NonABCKEY2fr, 0.0) * 100.0 AS NonABCKEY2fr
FROM P1
INNER JOIN P2
ON P2.ID = P1.ID;
答案3
得分: 1
不需要分开两个COUNT()函数的结果。<br/>
您可以使用AVG()聚合函数简化条件聚合：
SELECT ID,
100 * AVG(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY1_fR_NonABC,
100 * AVG(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY1_fR_ClassFilterABC,
100 * AVG(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY2_fR_NonABC,
100 * AVG(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY2_fR_ClassFilterABC
FROM #TempTable
GROUP BY ID;
请参见[演示][1]。<br/>
[1]: https://dbfiddle.uk/JPOumTPr
<details>
<summary>英文:</summary>
There is no need to divide the results of 2 aggregate `COUNT()`s.<br/>
You can simplify the conditional aggregation with `AVG()` aggregate function:
SELECT ID,
100 * AVG(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY1_fR_NonABC,
100 * AVG(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY1 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY1_fR_ClassFilterABC,
100 * AVG(CASE WHEN GroupCD <> 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY2_fR_NonABC,
100 * AVG(CASE WHEN GroupCD = 'ABC' AND SurrogateKEY2 IS NOT NULL THEN 1.0 ELSE 0.0 END) AS SurrogateKEY2_fR_ClassFilterABC
FROM #TempTable
GROUP BY ID;
See the [demo][1].<br/>
[1]: https://dbfiddle.uk/JPOumTPr
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论