SQL语句分组统计问题

runbean 2021-08-07 原文

统计各个年龄阶段的人数和性别

select
         ageGroup  as \'年龄段\'
       , cont(*)   as \'人数\'
       , sex       as \'性别\'
from
         (
                select
                       case
                              when age      >=1
                                     and age<=10
                                     then \'1-10\'
                              when age      >=11
                                     and age<=20
                                     then \'11-20\'
                              when age      >=21
                                     and age<=30
                                     then \'21-30\'
                              when age      >=31
                                     and age<=40
                                     then \'31-40\'
                                     else \'other\'
                       end as ageGroup
                     , uname
                     , sex
                from
                       table
         )
         t
group by
         ageGroup
       , sex

统计各个年龄人数占百分比

SELECT
         USER_AGE 年龄
       , COUNT(USER_AGE) 人数
       , CAST(CAST((COUNT(USER_AGE)/(
         (
                SELECT
                       COUNT(*)
                FROM
                       WORKER
         )
         *1.0)*100) AS DECIMAL(9,2)) AS VARCHAR)+\'%\' 所占比例
FROM
         WORKER
GROUP BY
         USER_AGE

postgresql统计

统计每小时的数量
思路: 格式化时间保留到小时,然后使用concat拼接00分00秒

SELECT
         COUNT(id)                                            cnt
       , CONCAT(to_char(create_time,\'yyyy-mm-dd hh24\'),\':00:00\') H
FROM
         table a
GROUP BY
                  CONCAT(to_char(create_time,\'yyyy-mm-dd hh24\'),\':00:00\')
ORDER BY
         H

每隔5秒统计一次
比如01秒、04秒统一标识为00秒，05、06、09秒统一标识为05秒
思路: 截取秒最后一位和5进行比较

SELECT
         tmp.newTime
       , tmp.name
       , COUNT(1) totalNum
FROM
         (
                  SELECT
                           t.id
                         , t.name
                         , t.create_time oldTime
                         , -- 原来的时间
                           CASE
                                    WHEN SUBSTR(to_char(t.create_time,\'yyyy-mm-dd hh24:mi:ss\'),19,1)::integer <5
                                             THEN CONCAT(SUBSTR(to_char(t.create_time,\'YYYY-mm-dd hh24:mi:ss\'),0,19),\'0\')
                                             ELSE CONCAT(SUBSTR(to_char(t.create_time,\'yyyy-mm-dd hh24:mi:ss\'),0,19),\'5\')
                           END AS newTime -- 时间段伪列
                  FROM
                           table t
                  ORDER BY
                           create_time ASC
         )
         tmp
GROUP BY
         tmp.newTime
       , tmp.name