作业
1、找出全部夺得3连贯的队伍2、找出每个id在在一天之内所有的波峰与波谷值3写sql3.1、每个id浏览时长、步长3.2、如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
1、找出全部夺得3连贯的队伍
数据：
team,year
活塞,1990
公牛,1991
公牛,1992
公牛,1993
火箭,1994
火箭,1995
公牛,1996
公牛,1997
公牛,1998
马刺,1999
湖人,2000
湖人,2001
湖人,2002
马刺,2003
活塞,2004
马刺,2005
热火,2006
马刺,2007
凯尔特人,2008
湖人,2009
湖人,2010
1.求连续值的则想到使用排序函数row_number()
2.根据队伍来分组，根据年份来排序
3.使用年份year -num 得到结果可以作为后面分组计算的依据
4.根据上面的第到的值去分组，并且统计其个数大于三的就是最终结果了
sql:
select team,num from (select team,year, (year - row_number() over(partition by team order by year)) num from t1) t2 group by num,team having count(*) >= 3;
2、找出每个id在在一天之内所有的波峰与波谷值
id,time,price
sh66688,9:35,29.48
sh66688,9:40,28.72
sh66688,9:45,27.74
sh66688,9:50,26.75
sh66688,9:55,27.13
sh66688,10:00,26.30
sh66688,10:05,27.09
sh66688,10:10,26.46
sh66688,10:15,26.11
sh66688,10:20,26.88
sh66688,10:25,27.49
sh66688,10:30,26.70
sh66688,10:35,27.57
sh66688,10:40,28.26
sh66688,10:45,28.03
sh66688,10:50,27.36
sh66688,10:55,26.48
sh66688,11:00,27.41
sh66688,11:05,26.70
sh66688,11:10,27.35
sh66688,11:15,27.35
sh66688,11:20,26.63
sh66688,11:25,26.35
sh66688,11:30,26.81
sh66688,13:00,29.45
sh66688,13:05,29.41
sh66688,13:10,29.10
sh66688,13:15,28.24
sh66688,13:20,28.20
sh66688,13:25,28.59
sh66688,13:30,29.49
sh66688,13:35,30.45
sh66688,13:40,30.31
sh66688,13:45,30.17
sh66688,13:50,30.55
sh66688,13:55,30.75
sh66688,14:00,30.03
sh66688,14:05,29.61
sh66688,14:10,29.96
sh66688,14:15,30.79
sh66688,14:20,29.82
sh66688,14:25,30.09
sh66688,14:30,29.61
sh66688,14:35,29.88
sh66688,14:40,30.36
sh66688,14:45,30.88
sh66688,14:50,30.73
sh66688,14:55,30.76
sh88888,9:35,67.23
sh88888,9:40,66.56
sh88888,9:45,66.73
sh88888,9:50,67.43
sh88888,9:55,67.49
sh88888,10:00,68.34
sh88888,10:05,68.13
sh88888,10:10,67.35
sh88888,10:15,68.13
sh88888,10:20,69.05
sh88888,10:25,69.82
sh88888,10:30,70.62
sh88888,10:35,70.59
sh88888,10:40,70.40
sh88888,10:45,70.29
sh88888,10:50,70.53
sh88888,10:55,70.92
sh88888,11:00,71.13
sh88888,11:05,70.24
sh88888,11:10,70.37
sh88888,11:15,69.79
sh88888,11:20,69.73
sh88888,11:25,70.52
sh88888,11:30,71.23
sh88888,13:00,72.85
sh88888,13:05,73.76
sh88888,13:10,74.72
sh88888,13:15,75.48
sh88888,13:20,75.80
sh88888,13:25,76.74
sh88888,13:30,77.22
sh88888,13:35,77.12
sh88888,13:40,76.90
sh88888,13:45,77.80
sh88888,13:50,78.75
sh88888,13:55,78.30
sh88888,14:00,78.68
sh88888,14:05,78.99
sh88888,14:10,78.35
sh88888,14:15,78.37
sh88888,14:20,78.07
sh88888,14:25,78.80
sh88888,14:30,79.78
sh88888,14:35,79.72
sh88888,14:40,80.71
sh88888,14:45,79.92
sh88888,14:50,80.49
sh88888,14:55,80.44
1.大于前后为波峰 小于前后为波谷
2.使用LAG函数，根据id分区，根据时间排序将前一天数据找出，如果没有则设置默认值为本身 并且设置列名p1.使用lead函数，根据id分区，根据时间排序将后一天数据找出，如果没有则设置默认值为本身并且设置列名p2.
3.上面的数据作为子查询，然后使用where筛选出(price >p1 and price >p2) 和 ( price <p1 and price <p2 )并且使用case when 函数给该列命名
sql:
select id,time,price,
case
when price >p1 and price >p2 then '波峰'
when price <p1 and price <p2 then '波谷'
end futer
from (
select id,time,price,LAG(price,1,price) over(partition by id order by time) p1,
LEAD(price,1,price) over(partition by id order by time) p2
from t2
) t3
where (price >p1 and price >p2) or ( price <p1 and price <p2 );
3写sql
id dt browseid
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:02 https://www.lagou.com/jobs/9590606.html?show=IEEE1FIJ3106A1H062HA
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:23 https://www.lagou.com/jobs/998375.html?show=EC1JGEC8G3HJC82JIHCD
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:09 https://www.lagou.com/jobs/8205098.html?show=G75J62JE63JE3678G98F
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:12 https://www.lagou.com/jobs/2280203.html?show=1957CGIA1702C1J9F0GH
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:31 https://www.lagou.com/jobs/5921958.html?show=BJ9CJJ6F0GH0CDGGHCCB
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 17:34 https://www.lagou.com/jobs/2569616.html?show=G5472AH6G1I61CGF9HGC
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:46 https://www.lagou.com/jobs/3892054.html?show=E771D8I4JJ0DE4DF575C
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:13 https://www.lagou.com/jobs/9559088.html?show=3EG4D1108IC3B446G2EB
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:14 https://www.lagou.com/jobs/3381768.html?show=99B480535EC2FA31DJ92
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:11 https://www.lagou.com/jobs/5100510.html?show=JGH3HJ36D7GHIEHEEFI6
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:20 https://www.lagou.com/jobs/2814357.html?show=6A6799246J9J4B6IC9HI
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:25 https://www.lagou.com/jobs/2428943.html?show=697DI68E5F133A1DD96D
934e8bee978a42c7a8dbb4cfa8af0b4f 2020/05/28 18:41 https://www.lagou.com/jobs/2790534.html?show=1C72FF96F549G4A458BI
32258fe7130844399859aec54b6df5ff 2020/05/28 03:47 https://www.lagou.com/jobs/4319618.html?show=A9IB685E7CJ9DIAB2244
32258fe7130844399859aec54b6df5ff 2020/05/28 03:33 https://www.lagou.com/jobs/1944013.html?show=A70H86DF1EHG2E57H1HE
32258fe7130844399859aec54b6df5ff 2020/05/28 03:21 https://www.lagou.com/jobs/1013342.html?show=366DJ2870404637EC19D
32258fe7130844399859aec54b6df5ff 2020/05/28 03:54 https://www.lagou.com/jobs/4952649.html?show=DGCC1FH06B69I9B1GA08
32258fe7130844399859aec54b6df5ff 2020/05/28 03:48 https://www.lagou.com/jobs/4427940.html?show=JAF2067192A1H53IJ00G
32258fe7130844399859aec54b6df5ff 2020/05/28 03:08 https://www.lagou.com/jobs/231554.html?show=I1J8G8075B7G5IDA326C
32258fe7130844399859aec54b6df5ff 2020/05/28 05:09 https://www.lagou.com/jobs/4799769.html?show=J7BGJ4B50GFHG4FEJCB6
32258fe7130844399859aec54b6df5ff 2020/05/28 05:26 https://www.lagou.com/jobs/7373006.html?show=6J9JJ89EADI7DI0H82C3
32258fe7130844399859aec54b6df5ff 2020/05/28 05:11 https://www.lagou.com/jobs/5766122.html?show=6J224ECEABC7C9I62763
32258fe7130844399859aec54b6df5ff 2020/05/28 05:34 https://www.lagou.com/jobs/2962929.html?show=GH06BC9D6I2G7H3D79B8
32258fe7130844399859aec54b6df5ff 2020/05/28 05:18 https://www.lagou.com/jobs/5653876.html?show=H426J08J6H4JJB74HFJE
32258fe7130844399859aec54b6df5ff 2020/05/28 05:50 https://www.lagou.com/jobs/7040422.html?show=0C78E264AHEADEJ26643
32258fe7130844399859aec54b6df5ff 2020/05/28 05:45 https://www.lagou.com/jobs/2961967.html?show=A4702EJ6E5DJIA475AF1
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:04 https://www.lagou.com/jobs/5552238.html?show=3I84DE05EH1AB6D13B3G
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:21 https://www.lagou.com/jobs/1558623.html?show=CC6C7J0G326G2BJ3D179
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:48 https://www.lagou.com/jobs/9974358.html?show=7HJ4BIAGHD73F49G9JJC
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:56 https://www.lagou.com/jobs/2628314.html?show=H1110A1AA14H64DA876C
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:50 https://www.lagou.com/jobs/6317002.html?show=664G909C9EG7JC63IB7D
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:53 https://www.lagou.com/jobs/1925810.html?show=FGEC1A7I60JJDAJGAF1A
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:00 https://www.lagou.com/jobs/5946589.html?show=240EB7E488G6FH0G27JF
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:48 https://www.lagou.com/jobs/776158.html?show=03FI36B82792CEBJHI29
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:14 https://www.lagou.com/jobs/97519.html?show=JHF32AB5EH58HEC2F63G
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:50 https://www.lagou.com/jobs/5196791.html?show=HDCJCA8JE1BF1IFE6HF6
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:58 https://www.lagou.com/jobs/6289105.html?show=I8D80BJFC3F3FEGGHA5C
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:13 https://www.lagou.com/jobs/7901649.html?show=2GHE5B24F5ABC13I6EB4
de0096ad04ec4273b0462c7da7d79653 2020/05/28 17:39 https://www.lagou.com/jobs/3214603.html?show=F6G8632470DAE5E760BG
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:15 https://www.lagou.com/jobs/6981846.html?show=1E7F19G856JA9JD8AB9D
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:43 https://www.lagou.com/jobs/1141030.html?show=5E96FFJA82E1I2BF2FEE
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:14 https://www.lagou.com/jobs/8929830.html?show=89H155HCJ41H228010I5
de0096ad04ec4273b0462c7da7d79653 2020/05/28 07:36 https://www.lagou.com/jobs/2646629.html?show=B38GA2D1E10EBFE8F6F7
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:24 https://www.lagou.com/jobs/7111580.html?show=FC6FD5F45B12ABIF02GD
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:06 https://www.lagou.com/jobs/8038667.html?show=HG4HE7CGI00A7A1F2J5F
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:51 https://www.lagou.com/jobs/4024837.html?show=JB27071067EGBE8D060C
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:33 https://www.lagou.com/jobs/7463120.html?show=D42J0IC234DIA481EF82
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:09 https://www.lagou.com/jobs/8292709.html?show=H96I861CGIGIF571H2JJ
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:24 https://www.lagou.com/jobs/5115760.html?show=H93JAJFDJH19HEF1E918
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:47 https://www.lagou.com/jobs/4543947.html?show=FG0BGA0CFDF6270IJE32
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 08:17 https://www.lagou.com/jobs/2188473.html?show=80JBIA9GFAJ76FD980AE
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:46 https://www.lagou.com/jobs/9320424.html?show=1G50E0G0804JAJH2HBA1
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:00 https://www.lagou.com/jobs/8308905.html?show=21DGJA045F8E64JHA0D6
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:08 https://www.lagou.com/jobs/9159707.html?show=44EDBC5B43A444FH001C
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:10 https://www.lagou.com/jobs/9532255.html?show=A1I8GI28GF0B14E97D64
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:51 https://www.lagou.com/jobs/9785185.html?show=FIHCCA16AJDA32EC4332
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:56 https://www.lagou.com/jobs/1117353.html?show=78GH99D70424B013G303
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:20 https://www.lagou.com/jobs/1029027.html?show=F31BH181E6E8JJ4AD295
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:16 https://www.lagou.com/jobs/9539487.html?show=47G414184H33E14DH159
307d9dce3b7f495ab8ad6033f8c54930 2020/05/28 18:10 https://www.lagou.com/jobs/8051736.html?show=JJ189D6F4HD6F0E7A2AG
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:51 https://www.lagou.com/jobs/5261931.html?show=7DA832A31BI430197F48
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:32 https://www.lagou.com/jobs/7521003.html?show=ACDFI9730A2B646I0270
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:11 https://www.lagou.com/jobs/3408361.html?show=999AGGBH0DC2E35J097B
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:32 https://www.lagou.com/jobs/874257.html?show=58IF72BB8F74ID23GE87
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:37 https://www.lagou.com/jobs/3485939.html?show=460DIAJ135CC950J3C77
f5ae36c6cdda40d5954e08a2d14954a7 2020/05/28 14:35 https://www.lagou.com/jobs/8439256.html?show=81II8DB2J2IF8AIFJ67F
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:22 https://www.lagou.com/jobs/673620.html?show=H02AJA95GBE98768ADHF
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:07 https://www.lagou.com/jobs/3039181.html?show=A4EDIFDEJB2J40I64F04
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:56 https://www.lagou.com/jobs/7363821.html?show=5I687EDH2C3A1JJAF57D
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:58 https://www.lagou.com/jobs/8879039.html?show=929I8CG2CDB9AE0268JI
80ea80b2e5a64cbebfaf34aa797125f0 2020/05/28 03:27 https://www.lagou.com/jobs/2273737.html?show=D8C799HJ092G9I4230EH
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:17 https://www.lagou.com/jobs/6261949.html?show=876583DAG4FIEI637F6E
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:46 https://www.lagou.com/jobs/6987631.html?show=936BIDF2F4352A39H6FF
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:17 https://www.lagou.com/jobs/2379044.html?show=6J2A9DDHB4787CD90134
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:35 https://www.lagou.com/jobs/4658655.html?show=F87D3565JD253ED6FIHE
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:56 https://www.lagou.com/jobs/8550343.html?show=DB23C4598E005CGHH06D
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:53 https://www.lagou.com/jobs/166497.html?show=B9B3GFIB5EIC9E32J5IJ
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:16 https://www.lagou.com/jobs/3463570.html?show=20BBBA585JF22I953GBG
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:23 https://www.lagou.com/jobs/4105412.html?show=IAG945B35D5DA6F1E992
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:52 https://www.lagou.com/jobs/6541296.html?show=47BH0G2A7IGFHIH61A85
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 02:45 https://www.lagou.com/jobs/8701046.html?show=IDE79JG4DI0J6508F0HH
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:32 https://www.lagou.com/jobs/9080852.html?show=5JA75D16G22BE0H881G2
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:47 https://www.lagou.com/jobs/7148755.html?show=F58GI58H74989HD65173
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:46 https://www.lagou.com/jobs/4610986.html?show=G24C84DCG9FD5GFBFCEE
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:54 https://www.lagou.com/jobs/2566998.html?show=90BIIIIA346E50A5DA67
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:17 https://www.lagou.com/jobs/7418962.html?show=9F3AB45F1C3HAH58B8B8
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:16 https://www.lagou.com/jobs/1307719.html?show=1J85244F2F81JCBGHH9C
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:56 https://www.lagou.com/jobs/8686135.html?show=73E0E5J74EA8A5B8C0FD
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:14 https://www.lagou.com/jobs/81114.html?show=DCIJI9H51I5BHGC1587E
95273392ab1a4579914273cdd1f3a3ae 2020/05/28 22:58 https://www.lagou.com/jobs/3454023.html?show=FI5EICD1F25F005J3CJG
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:13 https://www.lagou.com/jobs/1609611.html?show=87F3GAA5DH97H6G6I3J2
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:51 https://www.lagou.com/jobs/6306362.html?show=8775525F5EG213C94EE7
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:33 https://www.lagou.com/jobs/9309683.html?show=3HHEBFA7BA8J8BH3GGIG
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:37 https://www.lagou.com/jobs/3769247.html?show=BD7FE2HE8AED2F5J6818
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:56 https://www.lagou.com/jobs/2542380.html?show=DE8IH40GG47E096E0BE5
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:38 https://www.lagou.com/jobs/7732574.html?show=24AEBB54FA71D9F7JIDA
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:05 https://www.lagou.com/jobs/2349225.html?show=5JI210IFIJ0I707G4II7
022f86d4533740ad914f233cbd9c4430 2020/05/28 22:39 https://www.lagou.com/jobs/2872366.html?show=9BCGBGHBE73IG5AF4569
3.1、每个id浏览时长、步长
1.按照id分组，使用max(),min()函数计算出当天时间的最大最小值，并且将时间使用UNIX_TIMESTAMP(）函数按照格式转换为时间戳
2.使用最大值时间戳减去最小的值再去除以60就是分钟差了（时长），如果需要指定格式的话还可以使用round()指定保留小数点
sql:
select id,(UNIX_TIMESTAMP(max(dt),"yyyy/MM/dd HH:mm") -
UNIX_TIMESTAMP(min(dt),"yyyy/MM/dd HH:mm"))/60 ,count(*) as bc
from t3 group by id;
3.2、如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
1.使用lag()函数求出dt前一行的值，如果是第一行则使用本身，并且根据id分组 根据dt正序
2.将每一行dt与前一行dt转为时间戳进行相减，然后除以60转换为分钟数
3.使用casewhen 语句，大于30的则为1 小于30 的则为0
4.将结果作为子查询，然后在外面加一层查询，并且使用sum() 作为窗口函数，根据id分组，dt排序（当加了order by 后sum是计算的第一行到当前行的累加）
5.然后再去sum()每条记录的时长，，然后count()计算出步长，并且使用group by 以id 和前面的分组字段进行分组
sql:
select id,sum(sc) logTime, count(*) logCount from(
select id ,dt, fzField,sum(fzField) over(partition by id order by dt) fzFields,sc from (
select id,dt,
(
UNIX_TIMESTAMP(dt,"yyyy/MM/dd HH:mm")-
UNIX_TIMESTAMP(lag(dt,1,dt) over(partition by id order by dt) ,"yyyy/MM/dd HH:mm")
)/60 sc,
case when (UNIX_TIMESTAMP(dt,"yyyy/MM/dd HH:mm")-
UNIX_TIMESTAMP(lag(dt,1,dt) over(partition by id order by dt) ,"yyyy/MM/dd HH:mm"))/60 >30 then 1
else 0
end fzField
from t3
) b
) c group by id,fzFields;
