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>
|
