postgresql 如何统计某些人的年龄谁有一个日志记录从另一个表在sql？

hrirmatl 于 2022-11-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(118)

我想统计一下有多少18岁的人只在日志表中记录了一次。现在，如果我有一个输入了2次的人，我可以看到有2个18岁的人。我不能让它只出现一次。我该怎么做？？？
我的日志表和人员表通过card_id连接。我的日志表具有登录日期和card_id。
而我的成员表有birthdate和card_id列。
这是我提出的问题

select 
 card_id, sum("18") as "18" 
from 
 ( select logs.login, members.card_id, 
    count(distinct (case when 0 <= age and age <= 18 then age end)) as "18", 
    count(  (case when 19 <= age and age <= 30 then age end)) as "30",
    count ( (case when 31 <= age and age <= 50 then age end)) as "50" 
   from 
   (select login, date_part('year', age(birthdate)) as age, members.card_id as card_id, 
     logs.login 
     from members 
     left join logs on logs.card_id=members.card_id
    ) as members 
   left join logs on logs.card_id=members.card_id
   group by logs.login, members.card_id 
  ) as members
  where login <= '20221029' group by card_id;

我想创建这样一个表：

18 | 30 | 50 |
---------------
2  | 0  | 0

postgresql

来源：https://stackoverflow.com/questions/74232404/how-to-count-certain-the-ages-of-people-who-have-a-log-record-from-another-table

1条答案

按热度按时间

hfwmuf9z1#

计算 * 非重复 * card_id-s的数量。

select count(distinct card_id)
from members join logs using (card_id)
where extract('year' from age(birthdate)) = 18
  and login <= '20221029';

不相关，但您似乎将login存储为文本。这不是一个好主意。请改用date类型。
问题更新后添加

select  count(*) filter (where user_age = 18) as age_18,
        count(*) filter (where user_age between 19 and 30) as age_30,
        count(*) filter (where user_age between 31 and 50) as age_50
from
(
 select distinct on (card_id) 
    extract('year' from age(birthdate)) user_age
 from members inner join logs using (card_id)
 where login <= '20221029'
 order by card_id, login desc -- pick the latest login
) AS t;

赞(0）回复(0）举报 2022-11-04

我来回答

postgresql 如何统计某些人的年龄谁有一个日志记录从另一个表在sql？

1条答案

相关问题

热门标签

最新问答