postgresql：统计列中真值的出现次数

vyu0f0g1 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(458)

我有下表：

CREATE TABLE trips(
     trip_id int,
     true_foot boolean,
     true_bike boolean,
     true_bus boolean,
     true_car boolean,
     true_metro boolean
)

INSERT INTO trips (trip_id,true_foot,true_bike,true_bus,true_car,true_metro) 
VALUES 
  (563097,'t','f','f','f','f'),
  (596303,'f','f','f','t','f'),
  (595648,'f','f','f','t','f'),
  (566061,'t','f','f','f','f'),
  (566753,'t','f','f','f','f'),
  (561179,'t','f','f','f','f'),
  (535519,'f','f','f','f','f'),
  (548460,'t','f','f','f','f'),
  (543477,'f','f','f','t','f'),
  (540797,'t','f','f','f','f')

一行中只有一列具有真值（或无）。然后我要计算脚、自行车、公共汽车等的所有真值。。。

SELECT 
 COUNT(*) FILTER (WHERE true_foot IS TRUE ) AS 'walk',
 COUNT(*) FILTER (WHERE true_bike IS TRUE ) AS 'bike',
 COUNT(*) FILTER (WHERE true_bus IS TRUE) AS 'bus',
 COUNT(*) FILTER (WHERE true_car IS TRUE) AS 'car',
 COUNT(*) FILTER (WHERE true_metro IS TRUE) AS 'metro'

FROM trips

ERROR:  syntax error at or near "'walk'"
LINE 3:  COUNT(*) FILTER (WHERE true_foot IS TRUE ) AS 'walk',

sql postgresql count aggregate-functions

来源：https://stackoverflow.com/questions/62574516/postgresql-count-occurrence-of-true-values-in-columns

2条答案

按热度按时间

ecfdbz9o1#

单引号只能用于字符串和日期常量。你不需要它们，所以把它们拿走。
也， is true 实际上是多余的。所以：

SELECT COUNT(*) FILTER (WHERE true_foot) AS walk,
       COUNT(*) FILTER (WHERE true_bike) AS bike,
       COUNT(*) FILTER (WHERE true_bus) AS bus,
       COUNT(*) FILTER (WHERE true_car) AS car,
       COUNT(*) FILTER (WHERE true_metro) AS metro
FROM trips;

赞(0）回复(0）举报 2021-07-24

wn9m85ua2#

我喜欢用 SUM() 为了这个。您只需要将布尔值转换为整数值( true 变成 1 , false 变成 0 ).

SELECT 
    SUM( (true_food)::int )  AS walk,
    SUM( (true_bike)::int )  AS bike,
    SUM( (true_bus)::int )   AS bus,
    SUM( (true_car)::int )   AS car,
    SUM( (true_metro)::int ) AS metro
FROM trips

赞(0）回复(0）举报 2021-07-24

我来回答

postgresql：统计列中真值的出现次数

2条答案

相关问题

热门标签

最新问答