在 hive 中使用 lag 分析窗口函数可以更高效的得到我们想要的结果。
先造个数据，数据同上。

create database if not exists test comment '测试数据库';
drop table if exists test.work_time1;
create table test.work_time1 (
    user_id int comment '用户编号'
  , user_type int comment '用户类型：1表示上班，2表示下班'
  , create_time string comment '创建时间'
-- insert data
insert into test.work_time1(
    user_id
  , user_type
  , create_time
values(101, 1, '2020-01-05 09:00:00')
    , (101, 1, '2020-01-05 09:05:30')
    , (102, 1, '2020-01-05 09:15:02')
    , (101, 2, '2020-01-05 18:06:31')
    , (102, 2, '2020-01-05 18:30:31')
    , (102, 2, '2020-01-05 18:31:21')
    , (101, 1, '2020-01-06 08:45:22')
    , (101, 2, '2020-01-06 18:07:28')
    , (101, 1, '2020-01-07 09:30:02')
    , (101, 2, '2020-01-07 18:10:30')
    , (101, 2, '2020-01-07 18:15:21')
简要介绍一个 lag 函数，它的主要作用是将数据向后偏移：
 
lag(col, n, default) over(partition by … order by …)
lag中的内容：col表示字段名称，即对哪个字段使用lag函数；n表示滞后阶数；default表示滞后后没有值的位置用什么填充；
over中的内容：partition by 后接需要作为分区的字段（可多个，也可以不使用这个关键字，即所有数据为一个分区），order by 即在每个分区中排序
 
总而言之，对某个字段使用 lag 函数，按照指定分区或排序规则（可以不指定分区或排序规则），生成一个新的列。
 可以理解为，lag后字段看到的是原字段前一行中的值。
 假如一个员工在一段时间（几天）的上下班打卡操作为：12112的试想一下，我们要取的1之前是不是只有没有值（即1是该员工这几天第一个操作）或2，我们要取的2前一定是1。
 
with a as (
    select user_id
        , user_type
        , create_time
        , lag(user_type, 1, null) over(partition by user_id order by create_time) type_lag
    from xn_test.work_time1
b as (
    select a.user_id
        , a.user_type
        , a.create_time
        , row_number() over(partition by a.user_id order by a.create_time) rn
    from a
    where (a.user_type = 1 and (a.type_lag is null or a.type_lag = 2))
        or (a.user_type = 2 and a.type_lag = 1)
c as (
    select b.user_id
        , case when b.rn % 2 = 0 then b.rn - 1 else b.rn end
        , (unix_timestamp(max(b.create_time)) - unix_timestamp(min(b.create_time))) / 3600 work_time
    from b
    group by b.user_id
        , case when b.rn % 2 = 0 then b.rn - 1 else b.rn end
select c.user_id
    , sum(work_time) work_time
from c
                    本文交代了一种求上班总时长的特殊的业务场景，并造了一个玩具数据 ，分别用MySQL和Hive SQL给出了其计算逻辑。一、业务背景及口径说明指标 上班总时长 = SUM(下班时间-上班时间) ，但在特殊的业务场景下并没有那么容易得到，如：某员工一天的操作为：1→1→1→2→2（上班打卡为1，下班打卡为2）。因为可以重复打卡，该员工打完卡后不确定是否打卡成功，又进行多次操作。此时，定...
if（expr, v1, v2）其中：当表达式expr为真时返回v1，否则返回v2。
avg函数在计算某率的问题上比较好用，如某类工作的完成率，完成记为1，未完成记为0，则这一列的平均数即为完成率。
计算时长
timestampdiff(单位，time1， time2)，如timestampdiff(second，time1， time2)，time1和time2间隔了多少秒。
除法计算百分数
concat（round（v1*100，2），“%”）
				
最近在自改报表的时候，发现原始的计算工作时长存在一些误差，所以就修改了下。顺便做做笔记，方便以后查阅！
原始sql:
create or replace function f_number_to_time(as_number in number) return varchar2 is
  as_Result varchar2(100);
begin
 as_Result := nvl(sub
				
禅道上的统计报表非常匮乏，专业版的水晶报表又不划算，自己动手弄了套工时统计的方案 还挺好用的  希望对大家有所帮助 附件就是统计个人名下所有需求，任务和bug的sql语句，以及Excel统计模板 
操作步骤：
建议使用Navicat工具 
1.直接复制语句到Navicat查询编辑器
2.复制查询结果到Excel文档（参考压缩包中的Excel模板）
3.使用Excel中自带的插入数据透视表功能做统计报表
sql查询内容包括：
以人为经度查询产品需求数量
以产品为经度查询各需求所处阶段
以项目为经度查询当前所有任务归属人和所处阶段等等