postgresql 如何在GROUP BY子句中从SQL查询中删除NULL/不需要的值

70gysomp  于 2024-01-07  发布在  PostgreSQL
关注(0)|答案(1)|浏览(138)

这个查询的结果是自然地分组不需要的“组”。即在数据中有NULL和?值,我不想被用作组。我如何排除这些类型的值?
以下代码

SELECT 
    age, primary_fur_colour, location,
    SUM(CASE WHEN running = 'True' THEN 1 ELSE 0 END) AS total_running,
    SUM(CASE WHEN chasing = 'True' THEN 1 ELSE 0 END) AS total_chasing,
    SUM(CASE WHEN climbing = 'True' THEN 1 ELSE 0 END) AS total_climbing,
    SUM(CASE WHEN eating = 'True' THEN 1 ELSE 0 END) AS total_eating,
    SUM(CASE WHEN foraging = 'True' THEN 1 ELSE 0 END) AS total_foraging
FROM squirrel_data
GROUP BY age, primary_fur_colour, location

字符串
输出如下
| 年龄|原色|位置|总运转|全追逐|全爬升|总进食量|总觅食|
| --|--|--|--|--|--|--|--|
| | | | 0 | 0 | 0 | 0 | 0 |
| 什么?|灰色|地上| 1 | 0 | 0 | 0 | 0 |
| | 地上| 5 | 8 | 11 | 4 | 4 | 4 |
| | 接地平面| 0 | 0 | 0 | 1 | 5 | 5 |
| 成人|肉桂|接地平面| 68 | 15 | 23 | 70 | 165 |
| 成人||接地平面| 1 | 0 | 2 | 1 | 1 |
| 少年|肉桂|接地平面| 13 | 3 | 4 | 16 | 14 |
| 什么?|灰色|接地平面| 0 | 0 | 0 | 0 | 1 |
| | 接地平面| 0 | 0 | 0 | 0 | 1 | 1 |
| 成人|黑色|地上| 6 | 0 | 19 | 2 | 4 |
| 少年|灰色|地上| 13 | 13 | 56 | 22 | 17 |

jm2pwxwz

jm2pwxwz1#

您可能希望将所有这些条件和交换为常规聚合filter子句。如果它们的类型为boolean,则可以直接使用它们,而无需与'True'进行比较。
使用where跳过不需要的组:not in可以一次处理所有组,包括null s。

SELECT 
    age, primary_fur_colour, location,
    COUNT(*)FILTER(WHERE running  ) AS total_running,
    COUNT(*)FILTER(WHERE chasing  ) AS total_chasing,
    COUNT(*)FILTER(WHERE climbing ) AS total_climbing,
    COUNT(*)FILTER(WHERE eating   ) AS total_eating,
    COUNT(*)FILTER(WHERE foraging ) AS total_foraging
FROM squirrel_data
WHERE '?' NOT IN (age::text,
                  primary_fur_colour::text,
                  location::text )
GROUP BY age, primary_fur_colour, location;

字符串
或使用条件表达式重新分配到另一个(默认)组。

SELECT 
    COALESCE(NULLIF(age,'?'),               'Adult'       ) as age, 
    COALESCE(NULLIF(primary_fur_colour,'?'),'Gray'        ) as primary_fur_colour, 
    COALESCE(NULLIF(location,'?'),          'Ground Plane') as location,
    COUNT(*)FILTER(WHERE running  ) AS total_running,
    COUNT(*)FILTER(WHERE chasing  ) AS total_chasing,
    COUNT(*)FILTER(WHERE climbing ) AS total_climbing,
    COUNT(*)FILTER(WHERE eating   ) AS total_eating,
    COUNT(*)FILTER(WHERE foraging ) AS total_foraging
FROM squirrel_data
GROUP BY 1,2,3;

相关问题