您现在的位置:文学书籍 > 儿童诗歌

spark 累加历史 + 统计全部 + 行转列

发布时间:2019-08-13 11:53编辑:本站原创阅读(195)

    	spark 累加历史 + 统计全部 + 行转列

     累加历史所有:selectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateasc)assum_durationfromuserlogs_dateselectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateascand)assum_durationfromuserlogs_date)("pcode").orderBy("event_date")上边四种写法完全相等("pcode").orderBy("event_date").rowsBetween()累加前N天,后M天:假设N=3M=5selectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateascand)assum_durationfromuserlogs_("pcode").orderBy("event_date").rowsBetween()累加该分区内所有行selectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateascprecedingand)assum_durationfromuserlogs_("pcode").orderBy("event_date").rowsBetween()累加历史:分区内当天及之前所有写法1:selectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateasc)assum_durationfromuserlogs_date+-----+----------+------------+|pcode|event_date|sum_duration|+-----+----------+------------+|1438|2016-05-13|165||1438|2016-05-14|760||1438|2016-05-15|865||1629|2016-05-13|12340||1629|2016-05-14|26190||1629|2016-05-15|26417|+-----+----------+------------+累加历史:分区内当天及之前所有写法2:selectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateascrowsbetweenunboundedprecedingandcurrentrow)assum_durationfromuserlogs_date+-----+----------+------------+|pcode|event_date|sum_duration|+-----+----------+------------+|1438|2016-05-13|165||1438|2016-05-14|760||1438|2016-05-15|865||1629|2016-05-13|12340||1629|2016-05-14|26190||1629|2016-05-15|26417|+-----+----------+------------+累加当日和昨天:selectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateascrowsbetween1precedingandcurrentrow)assum_durationfromuserlogs_date+-----+----------+------------+|pcode|event_date|sum_duration|+-----+----------+------------+|1438|2016-05-13|165||1438|2016-05-14|760||1438|2016-05-15|700||1629|2016-05-13|12340||1629|2016-05-14|26190||1629|2016-05-15|14077|+-----+----------+------------+累加当日、昨日、明日:selectpcode,event_date,sum(duration)over(partitionbypcodeorderbyevent_dateascrowsbetween1precedingand1following)assum_durationfromuserlogs_date+-----+----------+------------+|pcode|event_date|sum_duration|+-----+----------+------------+|1438|2016-05-13|760||1438|2016-05-14|865||1438|2016-05-15|700||1629|2016-05-13|26190||1629|2016-05-14|26417||1629|2016-05-15|14077|+-----+----------+------------+累加分区内所有:当天和之前之后所有:+-----+----------+------------+|pcode|event_date|sum_duration|+-----+----------+------------+|1438|2016-05-13|865||1438|2016-05-14|865||1438|2016-05-15|865||1629|2016-05-13|26417||1629|2016-05-14|26417||1629|2016-05-15|26417|+-----+----------+------------+业务场景2:统计全部//+-----+----------+------------+|pcode|event_date|sum_duration|+-----+----------+------------+|null|null|27282||1438|null|865||1438|2016-05-13|165||1438|2016-05-14|595||1438|2016-05-15|105||1629|null|26417||1629|2016-05-13|12340||1629|2016-05-14|13850||1629|2016-05-15|227|+-----+----------+------------+。

    上一篇:小伙驾驶吊车进入火场勇救14人:做自己该做的事

    下一篇:没有了