英文:
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分组SELECTID,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_fRFROM #TempTableGROUP BY ID
-- 扩展查询以基于GroupCD = 'ABC' 或其他代码计算填充率SELECTID,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_ClassFilterABCFROM #TempTableGROUP 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:
SELECTID,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_fRFROM #TempTableGROUP 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
以下是翻译好的代码部分：
SELECTID,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_ClassFilterABCFROM #TempTableGROUP BY ID
请注意，上述代码是SQL查询的一部分，用于计算不同条件下的百分比。
英文:
Its can be done this way :
SELECTID,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_ClassFilterABCFROM #TempTableGROUP 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 ENDFROM TempTable),P1 AS(SELECT ID, [ABC] AS ABCKEY1fr, [NonABC] AS NonABCKEY1frFROM CTE AS DTPIVOT(MAX(SurrogateKEY1fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT),P2 AS(SELECT ID, [ABC] AS ABCKEY2fr, [NonABC] AS NonABCKEY2frFROM CTE AS DTPIVOT(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 NonABCKEY2frFROM P1INNER JOIN P2ON 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 ENDFROM TempTable),P1 AS(SELECT ID, [ABC] AS ABCKEY1fr, [NonABC] AS NonABCKEY1frFROM CTE AS DTPIVOT(MAX(SurrogateKEY1fr) FOR GrpKey IN([ABC], [NonABC])) AS PVT),P2 AS(SELECT ID, [ABC] AS ABCKEY2fr, [NonABC] AS NonABCKEY2frFROM CTE AS DTPIVOT(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 NonABCKEY2frFROM P1INNER JOIN P2ON 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_ClassFilterABCFROM #TempTableGROUP 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_ClassFilterABCFROM #TempTableGROUP BY ID;See the [demo][1].<br/>[1]: https://dbfiddle.uk/JPOumTPr</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论