Hive 算两时间差

最新推荐文章于 2023-09-18 16:02:04 发布

一个很老的小萌新

最新推荐文章于 2023-09-18 16:02:04 发布

阅读量1.8k

点赞数

分类专栏： SQL 文章标签：数据库 sql

本文链接：https://blog.csdn.net/dhq15800562693/article/details/127278635

版权

SQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

hive算时间差：CAST((unix_timestamp(process_dttm) - unix_timestamp(pay_dttm)) / 60 AS int)>10

-- 需求人：林楚君 (Ashley) 
-- 最近分区为全量数据


insert overwrite table dw_busdb.busticket_issue_successrate_and_timely_rate partition (d='${pre_day}')



select 
a.date
,a.failticket_order_cnt
-- ,b.successticket_order_cnt
,c.allticket_order_cnt - a.failticket_order_cnt as successticket_order_cnt
,c.allticket_order_cnt
,concat(cast(nvl((c.allticket_order_cnt - a.failticket_order_cnt )*100,0)/c.allticket_order_cnt as decimal(10,2)),'%') as success_order_rate


,immedi.whithin10min_order_cnt
,immedi.over10min_order_cnt
,immedi.all_nonappoint_order_cnt
,immedi.10min_order_rate

from 
(
      select  to_date(process_dttm) as date 
      ,count(distinct order_number) failticket_order_cnt
        FROM dw_busdb.tbl_order o
        WHERE o.d = '${pre_day}'
            AND process_dttm >= '2018-01-01'  --'${pre_day}'
            AND process_dttm < '${cur_day}'
            AND o.order_business_type = 'bus'
            and order_channel='ctrip' 
            and utm_source not like "ctripwx%"
            and utm_source not like "wxscan%" 
            and utm_source not like "ticketmachine%"
            and utm_source not like "tmv2%" 
            and utm_source not like "busweixin%" 
            and utm_source not like "offzhcz%"
            and process_state>5 and user_cancel=0  -- 出票失败
		group by to_date(process_dttm)	 			 
)a
left join 
(
      select  to_date(process_dttm) as date 
      ,count(distinct order_number) successticket_order_cnt
        FROM dw_busdb.tbl_order o
        WHERE o.d = '${pre_day}'
            AND process_dttm >=  '2018-01-01'  --'${pre_day}'
            AND process_dttm <  '${cur_day}'
            AND o.order_business_type = 'bus'
            and order_channel='ctrip' 
            and utm_source not like "ctripwx%"
            and utm_source not like "wxscan%" 
            and utm_source not like "ticketmachine%"
            and utm_source not like "tmv2%" 
            and utm_source not like "busweixin%" 
            and utm_source not like "offzhcz%"
            and process_state = 5  -- 出票成功
		group by to_date(process_dttm)	 
)b

on a.date = b.date


left join 
(
      select   to_date(process_dttm) as date
      ,count(distinct order_number) allticket_order_cnt
        FROM dw_busdb.tbl_order o
        WHERE o.d ='${pre_day}'
            AND process_dttm >= '2018-01-01'  --'${pre_day}'
            AND process_dttm <  '${cur_day}'
            AND o.order_business_type = 'bus'
            and order_channel='ctrip' 
            and utm_source not like "ctripwx%"
            and utm_source not like "wxscan%" 
            and utm_source not like "ticketmachine%"
            and utm_source not like "tmv2%" 
            and utm_source not like "busweixin%" 
            and utm_source not like "offzhcz%"
            and process_state>4 and user_cancel=0  -- 参与出票订单总量
			group by to_date(process_dttm)				
)c
on a.date = c.date

left join

(
        select a.date
		,a.whithin10min_order_cnt
        ,b.over10min_order_cnt
        ,c.all_nonappoint_order_cnt
        ,concat(cast(nvl(a.whithin10min_order_cnt*100,0)/c.all_nonappoint_order_cnt as decimal(10,2)),'%') as 10min_order_rate
        from 
        (
              select to_date(process_dttm) as date 
              ,count(distinct order_number) whithin10min_order_cnt
                FROM dw_busdb.tbl_order o
                WHERE o.d = '${pre_day}'
                    AND process_dttm >= '2018-01-01'  --'${pre_day}'
                    AND process_dttm < '${cur_day}'
                    AND o.order_business_type = 'bus'
                    and order_channel='ctrip' 
                    and utm_source not like "ctripwx%"
                    and utm_source not like "wxscan%" 
                    and utm_source not like "ticketmachine%"
                    and utm_source not like "tmv2%" 
                    and utm_source not like "busweixin%" 
                    and utm_source not like "offzhcz%"
                    and booking_flag in  (0,2)
                    and process_state>4 and  user_cancel=0
                    and CAST((unix_timestamp(process_dttm) - unix_timestamp(pay_dttm)) / 60 AS int)<=10
				group by to_date(process_dttm)	
        )a
        left join 
        (
              select  to_date(process_dttm) as date 
              ,count(distinct order_number) over10min_order_cnt
                FROM dw_busdb.tbl_order o
                WHERE o.d = '${pre_day}'
                    AND process_dttm >= '2018-01-01'  --'${pre_day}'
                    AND process_dttm <  '${cur_day}'
                    AND o.order_business_type = 'bus'
                    and order_channel='ctrip' 
                    and utm_source not like "ctripwx%"
                    and utm_source not like "wxscan%" 
                    and utm_source not like "ticketmachine%"
                    and utm_source not like "tmv2%" 
                    and utm_source not like "busweixin%" 
                    and utm_source not like "offzhcz%"
                    and booking_flag in (0,2)
                    and process_state>4 and  user_cancel=0
                    and CAST((unix_timestamp(process_dttm) - unix_timestamp(pay_dttm)) / 60 AS int)>10
				group by to_date(process_dttm)	
        )b
        on a.date = b.date
        left join 
        (
              select  to_date(process_dttm) as date 
              ,count(distinct order_number) all_nonappoint_order_cnt
                FROM dw_busdb.tbl_order o
                WHERE o.d = '${pre_day}'
                    AND process_dttm >= '2018-01-01'  --'${pre_day}'
                    AND process_dttm <  '${cur_day}'
                    AND o.order_business_type = 'bus'
                    and order_channel='ctrip' 
                    and utm_source not like "ctripwx%"
                    and utm_source not like "wxscan%" 
                    and utm_source not like "ticketmachine%"
                    and utm_source not like "tmv2%" 
                    and utm_source not like "busweixin%" 
                    and utm_source not like "offzhcz%"
                    and booking_flag in (0,2)
                    and process_state>4 and  user_cancel=0
				group by to_date(process_dttm)
        )c
        on a.date = c.date
)immedi

on a.date = immedi.date