Postgresql 用sql语句实现年龄分段统计

最新推荐文章于 2024-04-29 19:28:06 发布

中亿丰数字科技集团有限公司

最新推荐文章于 2024-04-29 19:28:06 发布

阅读量2.3k

点赞数 3

分类专栏： DTCloud 文章标签： sql

本文链接：https://blog.csdn.net/ekcchina/article/details/124996707

版权

DTCloud 专栏收录该内容

118 篇文章 8 订阅

订阅专栏

老人年龄图表男

        male_list = [0, 0, 0, 0, 0, 0]
        cr.execute(
            """select 
                    CASE WHEN age<50 THEN '0-50'
                         WHEN age<59 THEN '50-59'
                         WHEN age<69 THEN '60-69'
                         WHEN age<79 THEN '70-79'
                         WHEN age<89 THEN '80-89'
                         WHEN age<99 THEN '90-99'
                         WHEN age<200 THEN '100+'
                         ELSE 'other'
                    END as age,
                    count(*)
                from res_partner where gender ='male' and company_id = %s
                group by 
                    CASE WHEN age<50 THEN '0-50'
                         WHEN age<59 THEN '50-59'
                         WHEN age<69 THEN '60-69'
                         WHEN age<79 THEN '70-79'
                         WHEN age<89 THEN '80-89'
                         WHEN age<99 THEN '90-99'
                         WHEN age<200 THEN '100+'
                         ELSE 'other'
                    END
           """ % user.company_id.id)

        res = dict(((status['age'], status['count']) for status in cr.dictfetchall()))

        if res:
            male_list[0] = res.get('50-59', 0)
            male_list[1] = res.get('60-69', 0)
            male_list[2] = res.get('70-79', 0)
            male_list[3] = res.get('80-89', 0)
            male_list[4] = res.get('90-99', 0)
            male_list[5] = res.get('100+', 0)

在这里插入图片描述
同理我们也可实现其它关键字查询分组统计

        # 老人类型 饼图
        elderly_type_list = []
        cr.execute(
            """select 
                    CASE WHEN elderly_type='ordinary' THEN '普通老人'
                         WHEN elderly_type='fiveg' THEN '五保老人'
                         WHEN elderly_type='disabled' THEN '残疾老人'
                         ELSE 'other'
                    END as type,
                    count(*)
                from res_partner where company_id = %s
                group by 
                    CASE WHEN elderly_type='ordinary' THEN '普通老人'
                         WHEN elderly_type='fiveg' THEN '五保老人'
                         WHEN elderly_type='disabled' THEN '残疾老人'
                         ELSE 'other'
                END
           """ % user.company_id.id)

        res = dict(((status['type'], status['count']) for status in cr.dictfetchall()))

        if res:
            list_name = ['独居老人', '空槽老人', '五保老人','残疾老人','普通老人','失能老人','半失能老人']
            for l in list_name:
                values = {
                    'name': l,
                    'value': res.get(l, 0),
                }
                elderly_type_list.append(values)