pgsql 同比或者下降

IT的鱼

已于 2022-04-13 11:09:27 修改

阅读量652

点赞数

文章标签： postgresql

于 2022-04-13 11:01:33 首次发布

本文链接：https://blog.youkuaiyun.com/qq_32521313/article/details/124143149

版权

本文探讨了使用SQL查询统计不同类型的设备在指定时间段内创建次数的变化，通过计算百分比增减，揭示了total_tAS、type_hbc_tas和type_tbc_tas之间的对比情况。核心内容涉及时间区间筛选和数据趋势分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

  WITH total_t AS (
          select COUNT ( T.* ) AS COUNT FROM table t
 t.create_time BETWEEN to_timestamp( #{startTime},'yyyy-mm-dd')
                 and to_timestamp( #{endTime},'yyyy-mm-dd')

        ),
        type_hbc_t as(
     select COUNT ( T.* ) AS COUNT FROM table t
   t.create_time BETWEEN to_timestamp( #{startTime},'yyyy-mm-dd')
                 and to_timestamp( #{endTime},'yyyy-mm-dd')
        ),
        type_tbc_t as (
          select COUNT ( T.* ) AS COUNT FROM table t
 t.create_time BETWEEN to_timestamp( #{startTime},'yyyy-mm-dd')
                 and to_timestamp( #{endTime},'yyyy-mm-dd')
        )
        SELECT
        t2.count as total,
      (case when t3.COUNT - t2.COUNT > 0 then 'up' else 'down' end ) as hb_up_down,
		(case when t4.COUNT - t2.COUNT > 0 then 'up' else 'down' end ) as tb_up_down,
         CASE WHEN t3.COUNT - t2.COUNT > 0 THEN round((t3.COUNT - t2.COUNT)*100.0/t2.count::numeric ,2)
        else round(abs(t3.COUNT - t2.COUNT)*100.0/t2.count::numeric ,2)  END AS hb,
         CASE WHEN t4.COUNT - t2.COUNT > 0 THEN round((t4.COUNT - t2.COUNT) *100.0/t2.count::numeric ,2)
        else round(abs(t4.COUNT - t2.COUNT)*100.0/t4.count::numeric ,2)  END AS tb
        FROM total_t t2, type_hbc_t t3,type_tbc_t t4 





<sql id="hbConditionSql">
        <where>
            <if test="type != null and type != '' and type=='1'.toString()">
                <![CDATA[ T.create_time BETWEEN  #{startTime}::timestamp + '-1 day'
            and  #{endTime}::timestamp + '- 1 day' ]]>
            </if>

            <if test="type != null and type != '' and type=='2'.toString()">
                <![CDATA[ T.create_time BETWEEN  #{startTime}::timestamp + '-1 week'
            and  #{endTime}::timestamp + '- 1 week' ]]>
            </if>

            <if test="type != null and type != '' and type=='3'.toString()">
                <![CDATA[ T.create_time BETWEEN  #{startTime}::timestamp + '-1 month'
            and  #{endTime}::timestamp + '-1 month' ]]>
            </if>

            <if test="type != null and type != '' and type=='4'.toString()">
             <![CDATA[ T.create_time BETWEEN  #{startTime}::timestamp + '-3 month'
              and  #{endTime}::timestamp + '-3 month' ]]>
            </if>

            <if test="type != null and type != '' and type=='4'.toString()">
                <![CDATA[ T.create_time BETWEEN  #{startTime}::timestamp + '-1 year'
              and  #{endTime}::timestamp + '-1 year' ]]>
            </if>

        </where>
    </sql>