Sparksql的内置函数的使用以及案例

开发环境:spark:2.2.0

     工具:IDEA

     OS:Windows

数据文件:

001E8CB5AB11,ASUSTek,2018-07-12 14:00:57,2018-07-12 14:00:57,2018-07-12 14:00:57,未知,僵尸屏,0
0023242DDEB7,其他,2018-07-12 14:01:04,2018-07-12 14:01:04,2018-07-12 14:03:04,未知,僵尸屏,120
0026C5DC5555,其他,2018-07-12 13:59:49,2018-07-12 13:59:49,2018-07-12 14:05:49,未知-未知,广播,360
0026C5DC55AF,其他,2018-07-12 13:59:46,2018-07-12 13:59:46,2018-07-12 14:05:46,未知-未知,广播,360
0026C6DCB1A2,其他,2018-07-12 13:58:56,2018-07-12 13:58:56,2018-07-12 14:05:56,未知-未知,广播,420
,其他,2018-07-12 13:59:00,2018-07-12 13:59:00,2018-07-12 14:06:00,未知-未知,广播,420
00E04C68096A,其他,2018-07-12 14:04:16,2018-07-12 14:04:16,2018-07-12 14:04:16,未知,僵尸屏,0
00E04C682EE8,其他,2018-07-12 13:58:57,2018-07-12 13:58:57,2018-07-12 14:04:42,未知,僵尸屏,345
00E04C682FC2,其他,2018-07-12 14:04:35,2018-07-12 14:04:35,2018-07-12 14:04:35,未知,僵尸屏,0
00E04C683021,其他,2018-07-12 14:01:49,2018-07-12 14:01:49,2018-07-12 14:01:49,未知,僵尸屏,0
00E04C68304E,其他,2018-07-12 14:01:53,2018-07-12 14:01:53,2018-07-12 14:01:53,未知,僵尸屏,0
00E04C68308E,其他,2018-07-12 13:58:57,2018-07-12 13:58:57,2018-07-12 13:58:57,未知,僵尸屏,0
00E04C805917,其他,2018-07-12 14:01:49,2018-07-12 14:01:49,2018-07-12 14:03:46,未知,僵尸屏,117
01005E7FFFFA,其他,2018-07-12 14:00:35,2018-07-12 14:00:35,2018-07-12 14:06:36,未知,僵尸屏,361
0452F354175B,Apple,2018-07-12 14:04:48,2018-07-12 14:04:48,2018-07-12 14:04:48,未知,僵尸屏,0
081196ED5508,其他,2018-07-12 13:59:39,2018-07-12 13:59:39,2018-07-12 14:06:42,北京市-朝阳区,国贸CBD小屏组,423
08E6898E9FEF,其他,2018-07-12 14:02:04,2018-07-12 14:02:04,2018-07-12 14:02:04,未知,僵尸屏,0
0AA782A5A480,其他,2018-07-12 14:05:50,2018-07-12 14:05:50,2018-07-12 14:05:50,未知-未知,广播,0
0AB0BF18D980,其他,2018-07-12 14:01:27,2018-07-12 14:01:27,2018-07-12 14:01:28,未知-未知,广播,1
0C5101658E7E,Apple,2018-07-12 14:00:30,2018-07-12 14:00:30,2018-07-12 14:06:35,北京市-朝阳区,国贸CBD小屏组,365
0E3DFEB025EE,其他,2018-07-12 14:01:16,2018-07-12 14:01:16,2018-07-12 14:01:16,未知-未知,广播,0
0E9B36AE0439,其他,2018-07-12 14:01:59,2018-07-12 14:01:59,2018-07-12 14:01:59,未知-未知,广播,0
0EACDD37C80E,其他,2018-07-12 14:01:16,2018-07-12 14:01:16,2018-07-12 14:01:17,未知-未知,广播,1
123DE2CD7996,其他,2018-07-12 13:59:56,2018-07-12 13:59:56,2018-07-12 13:59:56,未知-未知,广播,0
14ABC5E7C34A,其他,2018-07-12 13:59:00,2018-07-12 13:59:00,2018-07-12 14:01:01,北京市-朝阳区,国贸CBD小屏组,121
14ABC5E7FA27,其他,2018-07-12 13:59:00,2018-07-12 13:59:00,2018-07-12 14:06:19,北京市-朝阳区,国贸CBD小屏组,439
14ABC5E80DCD,其他,2018-07-12 14:05:26,2018-07-12 14:05:26,2018-07-12 14:06:11,北京市-朝阳区,国贸CBD小屏组,45
14ABC5E8D5A5,其他,2018-07-12 13:59:02,2018-07-12 13:59:02,2018-07-12 14:06:42,未知,僵尸屏,460
14ABC5E9A232,其他,2018-07-12 13:59:15,2018-07-12 13:59:15,2018-07-12 14:06:43,未知,僵尸屏,448
16BFE5471B99,其他,2018-07-12 13:58:56,2018-07-12 13:58:56,2018-07-12 13:58:56,未知-未知,广播,0
1C9148606DDD,Apple,2018-07-12 14:00:04,2018-07-12 14:00:04,2018-07-12 14:05:51,北京市-朝阳区,国贸CBD小屏组,347
20AB378FFF58,Apple,2018-07-12 13:59:13,2018-07-12 13:59:13,2018-07-12 14:06:43,北京市-朝阳区,国贸CBD小屏组,450
20EE28579BA6,其他,2018-07-12 14:01:30,2018-07-12 14:01:30,2018-07-12 14:01:30,北京市-朝阳区,国贸CBD小屏组,0
20EE28881E8B,其他,2018-07-12 13:58:52,2018-07-12 13:58:52,2018-07-12 14:05:46,北京市-朝阳区,国贸CBD小屏组,414
22B6B6E440A5,其他,2018-07-12 14:01:51,2018-07-12 14:01:51,2018-07-12 14:03:22,未知-未知,广播,91
22F5B68E2E47,其他,2018-07-12 14:03:23,2018-07-12 14:03:23,2018-07-12 14:03:23,未知-未知,广播,0
22F8B3155513,其他,2018-07-12 14:03:26,2018-07-12 14:03:26,2018-07-12 14:03:26,未知-未知,广播,0
263A71D53FFB,其他,2018-07-12 14:01:02,2018-07-12 14:01:02,2018-07-12 14:01:02,未知-未知,广播,0
263E5C843DDE,其他,2018-07-12 13:59:11,2018-07-12 13:59:11,2018-07-12 13:59:11,未知-未知,广播,0
28010000404A,其他,2018-07-12 19:06:55,2018-07-12 19:06:55,2018-07-12 19:06:55,北京市-海定区,中关村鼎好电子大厦屏,7211
28010000404A,其他,2018-07-12 19:06:55,2018-07-12 19:06:55,2018-07-12 19:06:55,北京市-海定区,中关村鼎好电子大厦屏,7211
286C0722AB5B,Xiaomi,2018-07-12 13:59:01,2018-07-12 13:59:01,2018-07-12 14:06:42,未知,僵尸屏,461
286C07A9E76D,Xiaomi,2018-07-12 13:58:54,2018-07-12 13:58:54,2018-07-12 14:06:23,未知,僵尸屏,449
28D244480987,其他,2018-07-12 14:05:01,2018-07-12 14:05:01,2018-07-12 14:05:01,未知,僵尸屏,0
28D244F7FB6D,其他,2018-07-12 14:04:38,2018-07-12 14:04:38,2018-07-12 14:04:38,未知,僵尸屏,0
28F10E0DF818,其他,2018-07-12 14:02:02,2018-07-12 14:02:02,2018-07-12 14:06:35,未知,僵尸屏,273
2AF1ED7C29AC,其他,2018-07-12 14:04:04,2018-07-12 14:04:04,2018-07-12 14:04:04,未知-未知,广播,0
2C200BCF44CE,Apple,2018-07-12 13:59:15,2018-07-12 13:59:15,2018-07-12 13:59:15,未知,僵尸屏,0
2C27D71ED30F,其他,2018-07-12 14:06:03,2018-07-12 14:06:03,2018-07-12 14:06:03,未知,僵尸屏,0
2EA02A3B3651,其他,2018-07-12 14:00:47,2018-07-12 14:00:47,2018-07-12 14:00:47,未知-未知,广播,0
300ED5EE5DD5,HonHai,2018-07-12 13:58:52,2018-07-12 13:58:52,2018-07-12 14:06:43,未知,僵尸屏,471
300ED5EE5E90,HonHai,2018-07-12 13:58:55,2018-07-12 13:58:55,2018-07-12 14:06:43,未知,僵尸屏,468
300ED5EE5F66,HonHai,2018-07-12 13:58:52,2018-07-12 13:58:52,2018-07-12 14:06:43,未知,僵尸屏,471
300ED5EE5F73,HonHai,2018-07-12 13:58:53,2018-07-12 13:58:53,2018-07-12 14:06:37,未知,僵尸屏,464
300ED5EE5FAB,HonHai,2018-07-12 14:03:52,2018-07-12 14:03:52,2018-07-12 14:03:52,未知-未知,广播,0
300ED5EE5FEB,HonHai,2018-07-12 13:58:53,2018-07-12 13:58:53,2018-07-12 14:06:35,未知,僵尸屏,462
32C96E53E93B,其他,2018-07-12 14:01:29,2018-07-12 14:01:29,2018-07-12 14:01:29,未知-未知,广播,0
333300000001,其他,2018-07-12 13:59:04,2018-07-12 13:59:04,2018-07-12 14:02:35,未知,僵尸屏,211
333300010002,其他,2018-07-12 14:00:41,2018-07-12 14:00:41,2018-07-12 14:00:41,未知,僵尸屏,0
3333FF4FD8E3,其他,2018-07-12 14:01:07,2018-07-12 14:01:07,2018-07-12 14:01:07,未知,僵尸屏,0
3423870149D0,HonHai,2018-07-12 13:58:59,2018-07-12 13:58:59,2018-07-12 14:06:41,北京市-海定区,中关村鼎好电子大厦屏,462
342387458657,HonHai,2018-07-12 13:59:01,2018-07-12 13:59:01,2018-07-12 14:05:55,四川省-成都市,人民南路跳伞塔数码大厦屏,414
34CE00365F52,Xiaomi,2018-07-12 13:59:04,2018-07-12 13:59:04,2018-07-12 14:06:42,未知,僵尸屏,458
34CE00365F53,Xiaomi,2018-07-12 14:02:45,2018-07-12 14:02:45,2018-07-12 14:02:45,未知,僵尸屏,0
34CE008C161A,Xiaomi,2018-07-12 14:02:32,2018-07-12 14:02:32,2018-07-12 14:05:41,未知,僵尸屏,189
34CE00A5F9E0,Xiaomi,2018-07-12 13:59:11,2018-07-12 13:59:11,2018-07-12 14:06:29,未知,僵尸屏,438
34F39A7E5833,其他,2018-07-12 14:02:41,2018-07-12 14:02:41,2018-07-12 14:06:41,北京市-朝阳区,国贸CBD小屏组,240
34F39AC9D3EE,其他,2018-07-12 13:59:11,2018-07-12 13:59:11,2018-07-12 14:06:37,北京市-朝阳区,国贸CBD小屏组,446
360568556136,其他,2018-07-12 14:03:42,2018-07-12 14:03:42,2018-07-12 14:03:42,未知-未知,广播,0
36928E1BD4E1,其他,2018-07-12 14:03:38,2018-07-12 14:03:38,2018-07-12 14:03:38,未知-未知,广播,0
36DF8BF05003,其他,2018-07-12 14:03:03,2018-07-12 14:03:03,2018-07-12 14:03:03,未知-未知,广播,0
38A4ED0776D5,Xiaomi,2018-07-12 13:59:03,2018-07-12 13:59:03,2018-07-12 14:06:31,未知,僵尸屏,448
38E60A927B14,其他,2018-07-12 14:02:43,2018-07-12 14:02:43,2018-07-12 14:02:50,未知,僵尸屏,7
3C2EF9E10828,其他,2018-07-12 14:02:56,2018-07-12 14:02:56,2018-07-12 14:04:56,未知-未知,广播,120
3C2EF9EC943C,其他,2018-07-12 14:05:19,2018-07-12 14:05:19,2018-07-12 14:05:32,未知-未知,广播,13
3C3300AC6BE8,其他,2018-07-12 13:59:14,2018-07-12 13:59:14,2018-07-12 14:05:05,未知,僵尸屏,351
3C970E5EAC8E,其他,2018-07-12 13:59:26,2018-07-12 13:59:26,2018-07-12 14:01:40,未知,僵尸屏,134
3C970E5EB28B,其他,2018-07-12 13:59:10,2018-07-12 13:59:10,2018-07-12 14:06:42,未知,僵尸屏,452
3C970E6DE7C9,其他,2018-07-12 14:02:38,2018-07-12 14:02:38,2018-07-12 14:06:09,未知,僵尸屏,211
3E3D60DA1AEF,其他,2018-07-12 14:06:43,2018-07-12 14:06:43,2018-07-12 14:06:43,未知-未知,广播,0
3E540FBD7954,其他,2018-07-12 14:04:20,2018-07-12 14:04:20,2018-07-12 14:04:20,未知-未知,广播,0
407D0FF98F60,HUAWEI,2018-07-12 13:59:10,2018-07-12 13:59:10,2018-07-12 14:06:32,未知,僵尸屏,442
4200D28585D3,其他,2018-07-12 14:02:03,2018-07-12 14:02:03,2018-07-12 14:02:03,未知-未知,广播,0
42C3B892EFB5,其他,2018-07-12 13:59:30,2018-07-12 13:59:30,2018-07-12 13:59:30,未知-未知,广播,0
42C90F84A214,其他,2018-07-12 14:03:05,2018-07-12 14:03:05,2018-07-12 14:03:05,未知-未知,广播,0
4400107BAF3F,Apple,2018-07-12 14:00:02,2018-07-12 14:00:02,2018-07-12 14:03:02,未知-未知,广播,180
464BA9F371DA,其他,2018-07-12 14:06:09,2018-07-12 14:06:09,2018-07-12 14:06:09,未知-未知,广播,0
469C58896050,其他,2018-07-12 14:03:54,2018-07-12 14:03:54,2018-07-12 14:03:54,未知-未知,广播,0
46A57192928C,其他,2018-07-12 14:04:57,2018-07-12 14:04:57,2018-07-12 14:04:57,未知-未知,广播,0
480FCF30808D,其他,2018-07-12 14:04:51,2018-07-12 14:04:51,2018-07-12 14:04:51,未知,僵尸屏,0
480FCF42BE02,其他,2018-07-12 13:59:36,2018-07-12 13:59:36,2018-07-12 14:00:28,未知,僵尸屏,52
480FCF436A13,其他,2018-07-12 14:02:35,2018-07-12 14:02:35,2018-07-12 14:02:35,未知,僵尸屏,0
480FCF4C0F4E,其他,2018-07-12 14:00:51,2018-07-12 14:00:51,2018-07-12 14:06:13,未知,僵尸屏,322
480FCF5982E2,其他,2018-07-12 13:59:43,2018-07-12 13:59:43,2018-07-12 13:59:43,未知,僵尸屏,0
484D7EE21429,其他,2018-07-12 14:04:25,2018-07-12 14:04:25,2018-07-12 14:04:25,未知,僵尸屏,0
484D7EE2291C,其他,2018-07-12 14:00:25,2018-07-12 14:00:25,2018-07-12 14:00:25,未知,僵尸屏,0
484D7EE6482A,其他,2018-07-12 13:58:57,2018-07-12 13:58:57,2018-07-12 14:06:19,未知,僵尸屏,442
484D7EE90199,其他,2018-07-12 14:01:20,2018-07-12 14:01:20,2018-07-12 14:01:20,未知,僵尸屏,0
48D224953E1D,其他,2018-07-12 14:00:08,2018-07-12 14:00:08,2018-07-12 14:06:30,未知-未知,广播,382
4A746FB4E544,其他,2018-07-12 14:05:25,2018-07-12 14:05:25,2018-07-12 14:05:25,未知-未知,广播,0
4E71DE9EC470,其他,2018-07-12 14:02:11,2018-07-12 14:02:11,2018-07-12 14:02:27,未知-未知,广播,16
5065F33D23D7,其他,2018-07-12 14:05:30,2018-07-12 14:05:30,2018-07-12 14:05:30,未知,僵尸屏,0
5065F34779BA,其他,2018-07-12 13:59:04,2018-07-12 13:59:04,2018-07-12 14:06:26,未知,僵尸屏,442
5065F34AF99E,其他,2018-07-12 14:00:02,2018-07-12 14:00:02,2018-07-12 14:00:02,未知,僵尸屏,0
5065F34D0CE3,其他,2018-07-12 13:59:36,2018-07-12 13:59:36,2018-07-12 14:04:45,未知,僵尸屏,309
5065F35008E1,其他,2018-07-12 14:00:02,2018-07-12 14:00:02,2018-07-12 14:00:02,未知,僵尸屏,0
507B9D4D6BC1,其他,2018-07-12 13:59:07,2018-07-12 13:59:07,2018-07-12 14:06:22,未知,僵尸屏,435
507B9DE061A9,其他,2018-07-12 13:59:36,2018-07-12 13:59:36,2018-07-12 14:06:35,未知,僵尸屏,419
507B9DE14A26,其他,2018-07-12 14:00:57,2018-07-12 14:00:57,2018-07-12 14:01:27,未知,僵尸屏,30
509A4C152AAE,其他,2018-07-12 13:59:13,2018-07-12 13:59:13,2018-07-12 14:06:22,未知,僵尸屏,429
509A4C431E7A,其他,2018-07-12 14:00:31,2018-07-12 14:00:31,2018-07-12 14:00:31,未知,僵尸屏,0
509A4C438137,其他,2018-07-12 14:00:57,2018-07-12 14:00:57,2018-07-12 14:06:32,未知,僵尸屏,335
509A4C4B1DB1,其他,2018-07-12 14:04:16,2018-07-12 14:04:16,2018-07-12 14:04:16,未知,僵尸屏,0
525209C36338,其他,2018-07-12 14:04:51,2018-07-12 14:04:51,2018-07-12 14:04:51,未知-未知,广播,0
5433CB96997F,其他,2018-07-12 13:59:13,2018-07-12 13:59:13,2018-07-12 14:06:31,北京市-朝阳区,国贸CBD小屏组,438
54E1AD20F608,其他,2018-07-12 14:01:36,2018-07-12 14:01:36,2018-07-12 14:01:36,未知,僵尸屏,0
561A59EE2CA3,其他,2018-07-12 14:01:06,2018-07-12 14:01:06,2018-07-12 14:01:06,未知-未知,广播,0
568E02029CD4,其他,2018-07-12 14:02:29,2018-07-12 14:02:29,2018-07-12 14:02:29,未知-未知,广播,0
56D6904E6497,其他,2018-07-12 14:06:41,2018-07-12 14:06:41,2018-07-12 14:06:41,未知-未知,广播,0
5820B14FD8E3,其他,2018-07-12 13:59:10,2018-07-12 13:59:10,2018-07-12 14:06:09,未知,僵尸屏,419
5A29F8E9DD99,其他,2018-07-12 14:02:07,2018-07-12 14:02:07,2018-07-12 14:02:07,未知-未知,广播,0
5A6CEECC9C0F,其他,2018-07-12 14:03:46,2018-07-12 14:03:46,2018-07-12 14:03:46,未知-未知,广播,0
5A71A4822891,其他,2018-07-12 14:01:08,2018-07-12 14:01:08,2018-07-12 14:01:09,未知-未知,广播,1
5CADCF77DB62,Apple,2018-07-12 14:01:55,2018-07-12 14:01:55,2018-07-12 14:05:32,未知,僵尸屏,217
5CE0C5127190,其他,2018-07-12 13:59:04,2018-07-12 13:59:04,2018-07-12 14:06:05,四川省-成都市,人民南路跳伞塔数码大厦屏,421
5CE0C519E727,其他,2018-07-12 14:00:32,2018-07-12 14:00:32,2018-07-12 14:05:22,四川省-成都市,人民南路跳伞塔数码大厦屏,290
5CE0C519E74A,其他,2018-07-12 13:58:56,2018-07-12 13:58:56,2018-07-12 14:06:41,未知,僵尸屏,465
5CE0C519E777,其他,2018-07-12 13:59:17,2018-07-12 13:59:17,2018-07-12 14:06:08,北京市-朝阳区,国贸CBD小屏组,411
60F81DAC5A68,Apple,2018-07-12 13:59:03,2018-07-12 13:59:03,2018-07-12 14:06:35,未知,僵尸屏,452
617461010802,其他,2018-07-12 13:59:53,2018-07-12 13:59:53,2018-07-12 13:59:53,未知,僵尸屏,0
626126E68717,其他,2018-07-12 14:04:59,2018-07-12 14:04:59,2018-07-12 14:04:59,未知-未知,广播,0
62A0A77197ED,其他,2018-07-12 14:01:37,2018-07-12 14:01:37,2018-07-12 14:01:37,未知-未知,广播,0
64A2F924BBF5,其他,2018-07-12 13:58:54,2018-07-12 13:58:54,2018-07-12 14:04:57,北京市-朝阳区,国贸CBD小屏组,363
64B0A61B5116,Apple,2018-07-12 13:59:03,2018-07-12 13:59:03,2018-07-12 14:06:21,北京市-朝阳区,国贸CBD小屏组,438
66499E6E806C,其他,2018-07-12 13:59:04,2018-07-12 13:59:04,2018-07-12 13:59:04,未知-未知,广播,0
66FACD823429,其他,2018-07-12 14:04:03,2018-07-12 14:04:03,2018-07-12 14:04:04,未知-未知,广播,1
68AB1E1E476B,其他,2018-07-12 14:03:27,2018-07-12 14:03:27,2018-07-12 14:03:28,未知-未知,广播,1
68DBCA4B2398,Apple,2018-07-12 14:01:09,2018-07-12 14:01:09,2018-07-12 14:01:09,北京市-朝阳区,国贸CBD小屏组,0
68F728099B93,其他,2018-07-12 13:58:57,2018-07-12 13:58:57,2018-07-12 14:04:29,未知,僵尸屏,332
68F72809A006,其他,2018-07-12 14:01:56,2018-07-12 14:01:56,2018-07-12 14:01:56,未知,僵尸屏,0
68F7281D8047,其他,2018-07-12 13:58:54,2018-07-12 13:58:54,2018-07-12 14:04:19,未知,僵尸屏,325
68F7283626B2,其他,2018-07-12 13:58:54,2018-07-12 13:58:54,2018-07-12 14:06:39,未知,僵尸屏,465
68F7288D4ECE,其他,2018-07-12 14:04:16,2018-07-12 14:04:16,2018-07-12 14:04:22,未知,僵尸屏,6
68F728A1D100,其他,2018-07-12 13:59:30,2018-07-12 13:59:30,2018-07-12 14:04:29,未知,僵尸屏,299
68F728BA1A3F,其他,2018-07-12 13:59:07,2018-07-12 13:59:07,2018-07-12 14:05:50,未知,僵尸屏,403
68F728C18AFE,其他,2018-07-12 13:59:10,2018-07-12 13:59:10,2018-07-12 13:59:10,未知,僵尸屏,0
68F728C23AC3,其他,2018-07-12 14:02:45,2018-07-12 14:02:45,2018-07-12 14:06:35,未知,僵尸屏,230
6A2B0D59EED7,其他,2018-07-12 14:04:28,2018-07-12 14:04:28,2018-07-12 14:04:28,未知-未知,广播,0
6AB4554CF30C,其他,2018-07-12 14:02:38,2018-07-12 14:02:38,2018-07-12 14:02:38,未知-未知,广播,0
6C72E73965AC,Apple,2018-07-12 13:59:15,2018-07-12 13:59:15,2018-07-12 14:00:50,未知-未知,广播,95
6C72E7594DEB,Apple,2018-07-12 14:02:32,2018-07-12 14:02:32,2018-07-12 14:02:34,北京市-朝阳区,国贸CBD小屏组,2
6CC217226B22,其他,2018-07-12 14:00:25,2018-07-12 14:00:25,2018-07-12 14:02:54,未知,僵尸屏,149
6EC677CF6B23,其他,2018-07-12 14:01:45,2018-07-12 14:01:45,2018-07-12 14:01:45,未知-未知,广播,0
70480F1BE7A8,Apple,2018-07-12 13:58:59,2018-07-12 13:58:59,2018-07-12 14:05:43,未知-未知,广播,404
70480F476EC1,Apple,2018-07-12 14:01:24,2018-07-12 14:01:24,2018-07-12 14:06:29,北京市-朝阳区,国贸CBD小屏组,305
70F08783BECD,Apple,2018-07-12 13:58:54,2018-07-12 13:58:54,2018-07-12 14:06:38,北京市-朝阳区,国贸CBD小屏组,464
727D7D76FEB1,其他,2018-07-12 14:02:22,2018-07-12 14:02:22,2018-07-12 14:02:23,未知-未知,广播,1
74A528BCAABD,HUAWEI,2018-07-12 13:58:55,2018-07-12 13:58:55,2018-07-12 14:04:32,北京市-朝阳区,国贸CBD小屏组,337
7673F615363F,其他,2018-07-12 14:02:11,2018-07-12 14:02:11,2018-07-12 14:02:11,未知-未知,广播,0
7811DC483347,Xiaomi,2018-07-12 13:59:14,2018-07-12 13:59:14,2018-07-12 14:01:04,未知,僵尸屏,110
784F43BD2CE3,Apple,2018-07-12 13:59:24,2018-07-12 13:59:24,2018-07-12 14:01:18,未知,僵尸屏,114
784F43C1618D,Apple,2018-07-12 13:59:25,2018-07-12 13:59:25,2018-07-12 14:05:55,北京市-朝阳区,国贸CBD小屏组,390
78ACC0B47587,其他,2018-07-12 13:59:00,2018-07-12 13:59:00,2018-07-12 14:05:27,未知,僵尸屏,387
78E3B5D20296,其他,2018-07-12 13:59:20,2018-07-12 13:59:20,2018-07-12 13:59:20,未知,僵尸屏,0
78E7D1C2B47E,其他,2018-07-12 14:04:19,2018-07-12 14:04:19,2018-07-12 14:04:19,未知,僵尸屏,0
78E7D1C58D0E,其他,2018-07-12 14:02:48,2018-07-12 14:02:48,2018-07-12 14:02:48,未知,僵尸屏,0
78E7D1C58E24,其他,2018-07-12 14:01:23,2018-07-12 14:01:23,2018-07-12 14:01:23,未知,僵尸屏,0
78E7D1D92219,其他,2018-07-12 14:01:40,2018-07-12 14:01:40,2018-07-12 14:03:53,未知,僵尸屏,133
78E7D1D92235,其他,2018-07-12 14:02:38,2018-07-12 14:02:38,2018-07-12 14:02:38,未知,僵尸屏,0
7A8210E5CA96,其他,2018-07-12 14:01:38,2018-07-12 14:01:38,2018-07-12 14:01:39,未知-未知,广播,1
7AB3CDEF05D4,其他,2018-07-12 14:04:48,2018-07-12 14:04:48,2018-07-12 14:05:10,未知-未知,广播,22
7E35C9726EBF,其他,2018-07-12 14:03:33,2018-07-12 14:03:33,2018-07-12 14:03:33,未知-未知,广播,0
7E4AE2267C39,其他,2018-07-12 14:05:54,2018-07-12 14:05:54,2018-07-12 14:05:55,未知-未知,广播,1
7EA6AEC5D494,其他,2018-07-12 14:03:16,2018-07-12 14:03:16,2018-07-12 14:03:22,未知-未知,广播,6
7EFEA9FDAD1D,其他,2018-07-12 13:58:52,2018-07-12 13:58:52,2018-07-12 13:58:58,未知-未知,广播,6
80717A5D00E7,HUAWEI,2018-07-12 14:00:16,2018-07-12 14:00:16,2018-07-12 14:06:01,未知,僵尸屏,345
8243455B963D,其他,2018-07-12 14:02:35,2018-07-12 14:02:35,2018-07-12 14:02:35,未知-未知,广播,0
826AD7ABB8A8,其他,2018-07-12 14:02:18,2018-07-12 14:02:18,2018-07-12 14:02:19,未知-未知,广播,1
8441678BFAA3,其他,2018-07-12 14:03:35,2018-07-12 14:03:35,2018-07-12 14:03:41,北京市-朝阳区,国贸CBD小屏组,6
865CAED88A76,其他,2018-07-12 14:06:04,2018-07-12 14:06:04,2018-07-12 14:06:05,未知-未知,广播,1
86714590D43F,其他,2018-07-12 14:05:58,2018-07-12 14:05:58,2018-07-12 14:05:58,未知-未知,广播,0
86814B2B907F,其他,2018-07-12 14:03:34,2018-07-12 14:03:34,2018-07-12 14:03:34,未知-未知,广播,0
86E5DF74D12A,其他,2018-07-12 14:01:10,2018-07-12 14:01:10,2018-07-12 14:01:45,未知-未知,广播,35
8AF35338839D,其他,2018-07-12 14:03:43,2018-07-12 14:03:43,2018-07-12 14:03:43,未知-未知,广播,0
8C34FD58DAEA,HUAWEI,2018-07-12 13:59:49,2018-07-12 13:59:49,2018-07-12 14:04:24,北京市-朝阳区,国贸CBD小屏组,275
8CA9823E26DC,其他,2018-07-12 13:59:02,2018-07-12 13:59:02,2018-07-12 14:06:42,未知,僵尸屏,460
8CDCD42DDE4D,其他,2018-07-12 13:59:10,2018-07-12 13:59:10,2018-07-12 14:05:30,未知,僵尸屏,380
8CDCD42DDE61,其他,2018-07-12 14:04:32,2018-07-12 14:04:32,2018-07-12 14:04:32,未知,僵尸屏,0
8CDCD4301844,其他,2018-07-12 14:00:57,2018-07-12 14:00:57,2018-07-12 14:00:57,未知,僵尸屏,0
8CDCD4301AB3,其他,2018-07-12 14:02:12,2018-07-12 14:02:12,2018-07-12 14:04:22,未知,僵尸屏,130
8CDCD4301B1D,其他,2018-07-12 14:02:12,2018-07-12 14:02:12,2018-07-12 14:02:25,未知,僵尸屏,13
8CDCD4301B2E,其他,2018-07-12 14:00:48,2018-07-12 14:00:48,2018-07-12 14:06:19,未知,僵尸屏,331
8CDCD4311C78,其他,2018-07-12 13:59:17,2018-07-12 13:59:17,2018-07-12 13:59:17,未知,僵尸屏,0
8CDCD4334EB8,其他,2018-07-12 14:02:35,2018-07-12 14:02:35,2018-07-12 14:02:35,未知,僵尸屏,0
8CDCD4334F37,其他,2018-07-12 14:00:35,2018-07-12 14:00:35,2018-07-12 14:05:27,未知,僵尸屏,292
8CDCD4334F6A,其他,2018-07-12 13:59:07,2018-07-12 13:59:07,2018-07-12 14:05:11,未知,僵尸屏,364
8E26F8DC15EE,其他,2018-07-12 13:59:08,2018-07-12 13:59:08,2018-07-12 13:59:08,未知-未知,广播,0
8E5F2226CD50,其他,2018-07-12 14:01:46,2018-07-12 14:01:46,2018-07-12 14:01:46,未知-未知,广播,0
9202D3982BA3,其他,2018-07-12 14:03:27,2018-07-12 14:03:27,2018-07-12 14:03:40,未知-未知,广播,13
963075096988,其他,2018-07-12 14:05:06,2018-07-12 14:05:06,2018-07-12 14:05:06,未知-未知,广播,0
985AEBC6B4DF,Apple,2018-07-12 14:00:09,2018-07-12 14:00:09,2018-07-12 14:00:09,未知,僵尸屏,0
9C2EA1FADCD1,其他,2018-07-12 13:59:28,2018-07-12 13:59:28,2018-07-12 14:06:31,未知-未知,广播,423
9C443D1952FA,其他,2018-07-12 13:59:07,2018-07-12 13:59:07,2018-07-12 14:06:33,未知,僵尸屏,446
9CA5C0BD6F6D,vivo,2018-07-12 14:00:00,2018-07-12 14:00:00,2018-07-12 14:00:45,四川省-成都市,人民南路跳伞塔数码大厦屏,45
9CB6D00D99FD,其他,2018-07-12 13:58:58,2018-07-12 13:58:58,2018-07-12 14:06:37,未知-未知,广播,459
9CE33F33F576,其他,2018-07-12 14:03:32,2018-07-12 14:03:32,2018-07-12 14:03:49,北京市-朝阳区,国贸CBD小屏组,17
9CF48E3335F5,Apple,2018-07-12 14:01:13,2018-07-12 14:01:13,2018-07-12 14:06:33,北京市-朝阳区,国贸CBD小屏组,320
9CF48E43079E,Apple,2018-07-12 13:59:01,2018-07-12 13:59:01,2018-07-12 14:06:07,未知,僵尸屏,426
9CFC01682E78,Apple,2018-07-12 14:03:18,2018-07-12 14:03:18,2018-07-12 14:03:18,北京市-朝阳区,国贸CBD小屏组,0
9E7EE62C840B,其他,2018-07-12 14:06:13,2018-07-12 14:06:13,2018-07-12 14:06:14,未知-未知,广播,1
9E850CB1C147,其他,2018-07-12 14:05:22,2018-07-12 14:05:22,2018-07-12 14:05:22,未知-未知,广播,0
9ED082F4539F,其他,2018-07-12 13:59:15,2018-07-12 13:59:15,2018-07-12 13:59:15,未知-未知,广播,0
A0086F310909,HUAWEI,2018-07-12 13:59:47,2018-07-12 13:59:47,2018-07-12 14:05:54,北京市-朝阳区,国贸CBD小屏组,367
A0086F31090A,HUAWEI,2018-07-12 13:58:56,2018-07-12 13:58:56,2018-07-12 14:06:37,北京市-海定区,中关村鼎好电子大厦屏,461
A0086F31090E,HUAWEI,2018-07-12 13:58:53,2018-07-12 13:58:53,2018-07-12 14:06:09,北京市-朝阳区,国贸CBD小屏组,436
A057E36FD3E6,其他,2018-07-12 13:58:54,2018-07-12 13:58:54,2018-07-12 14:05:55,北京市-朝阳区,国贸CBD小屏组,421
A20D43A7B97D,其他,2018-07-12 14:03:12,2018-07-12 14:03:12,2018-07-12 14:05:36,未知-未知,广播,144
A211DDAB06FB,其他,2018-07-12 13:59:02,2018-07-12 13:59:02,2018-07-12 13:59:02,未知-未知,广播,0
A2578286415A,其他,2018-07-12 14:02:38,2018-07-12 14:02:38,2018-07-12 14:02:38,未知-未知,广播,0
A25FA9400829,其他,2018-07-12 14:06:43,2018-07-12 14:06:43,2018-07-12 14:06:44,未知-未知,广播,1
A41731F375EB,HonHai,2018-07-12 14:03:03,2018-07-12 14:03:03,2018-07-12 14:03:03,未知-未知,广播,0
A493FE3F6F08,其他,2018-07-12 13:58:52,2018-07-12 13:58:52,2018-07-12 14:06:43,未知,僵尸屏,471
A4CAA00A81C7,HUAWEI,2018-07-12 14:05:27,2018-07-12 14:05:27,2018-07-12 14:05:27,未知,僵尸屏,0
A679D542B052,其他,2018-07-12 14:02:35,2018-07-12 14:02:35,2018-07-12 14:02:35,未知-未知,广播,0
A67E6B8751A9,其他,2018-07-12 14:06:35,2018-07-12 14:06:35,2018-07-12 14:06:35,未知-未知,广播,0
A6FFFFFF404A,其他,2018-07-12 14:04:32,2018-07-12 14:04:32,2018-07-12 14:04:32,北京市-朝阳区,国贸CBD小屏组,0
A8667F624EE6,Apple,2018-07-12 14:00:33,2018-07-12 14:00:33,2018-07-12 14:00:33,北京市-朝阳区,国贸CBD小屏组,0
A8FFFFFF404A,其他,2018-07-12 14:00:01,2018-07-12 14:00:01,2018-07-12 14:00:01,北京市-海定区,中关村鼎好电子大厦屏,0
A9FFFFFF6F08,其他,2018-07-12 14:01:43,2018-07-12 14:01:43,2018-07-12 14:01:43,北京市-朝阳区,国贸CBD小屏组,0
AACCBD34BAD6,其他,2018-07-12 14:04:01,2018-07-12 14:04:01,2018-07-12 14:04:01,未知-未知,广播,0
AAE7B712600A,其他,2018-07-12 14:03:17,2018-07-12 14:03:17,2018-07-12 14:03:20,未知-未知,广播,3
AAFFFFFF6F08,其他,2018-07-12 14:02:00,2018-07-12 14:02:00,2018-07-12 14:03:26,北京市-朝阳区,国贸CBD小屏组,86
ABFFFFFF6F08,其他,2018-07-12 14:01:04,2018-07-12 14:01:04,2018-07-12 14:02:24,北京市-朝阳区,国贸CBD小屏组,80
ACFFFFFF404A,其他,2018-07-12 14:00:36,2018-07-12 14:00:36,2018-07-12 14:00:36,未知,僵尸屏,0
ADFFFFFF6F08,其他,2018-07-12 14:01:45,2018-07-12 14:01:45,2018-07-12 14:01:45,北京市-朝阳区,国贸CBD小屏组,0
AE3DA8C9C025,其他,2018-07-12 14:03:24,2018-07-12 14:03:24,2018-07-12 14:03:40,未知-未知,广播,16
AE862332942D,其他,2018-07-12 14:06:33,2018-07-12 14:06:33,2018-07-12 14:06:34,未知-未知,广播,1
AE8AD572A19E,其他,2018-07-12 14:04:15,2018-07-12 14:04:15,2018-07-12 14:04:15,未知-未知,广播,0
AEFFFFFF404A,其他,2018-07-12 14:02:50,2018-07-12 14:02:50,2018-07-12 14:02:50,北京市-海定区,中关村鼎好电子大厦屏,0
AEFFFFFF6F08,其他,2018-07-12 14:01:17,2018-07-12 14:01:17,2018-07-12 14:05:44,北京市-朝阳区,国贸CBD小屏组,267
AFFFFFFF404A,其他,2018-07-12 14:04:21,2018-07-12 14:04:21,2018-07-12 14:04:21,北京市-朝阳区,国贸CBD小屏组,0
AFFFFFFF6F08,其他,2018-07-12 14:00:13,2018-07-12 14:00:13,2018-07-12 14:06:26,北京市-朝阳区,国贸CBD小屏组,373
B0FFFFFF404A,其他,2018-07-12 13:59:22,2018-07-12 13:59:22,2018-07-12 14:06:15,北京市-海定区,中关村鼎好电子大厦屏,413
B0FFFFFF6F08,其他,2018-07-12 14:00:14,2018-07-12 14:00:14,2018-07-12 14:05:21,北京市-海定区,中关村鼎好电子大厦屏,307
B1FFFFFF404A,其他,2018-07-12 14:00:51,2018-07-12 14:00:51,2018-07-12 14:00:51,北京市-朝阳区,国贸CBD小屏组,0
B1FFFFFF6F08,其他,2018-07-12 14:00:24,2018-07-12 14:00:24,2018-07-12 14:02:11,未知,僵尸屏,107
B279695FAE3C,其他,2018-07-12 14:02:44,2018-07-12 14:02:44,2018-07-12 14:02:44,未知-未知,广播,0
B29508D7A6F8,其他,2018-07-12 14:04:04,2018-07-12 14:04:04,2018-07-12 14:04:04,未知-未知,广播,0
B2C08E9AD237,其他,2018-07-12 14:02:46,2018-07-12 14:02:46,2018-07-12 14:02:46,未知-未知,广播,0
B2F2FC1243C0,其他,2018-07-12 14:02:31,2018-07-12 14:02:31,2018-07-12 14:02:31,未知-未知,广播,0
B2FBB64DCF85,其他,2018-07-12 14:00:31,2018-07-12 14:00:31,2018-07-12 14:00:31,未知-未知,广播,0
B2FFFFFF404A,其他,2018-07-12 14:03:52,2018-07-12 14:03:52,2018-07-12 14:06:41,北京市-海定区,中关村鼎好电子大厦屏,169
B3FFFFFF404A,其他,2018-07-12 14:02:47,2018-07-12 14:02:47,2018-07-12 14:02:47,未知,僵尸屏,0
B3FFFFFF6F08,其他,2018-07-12 14:00:25,2018-07-12 14:00:25,2018-07-12 14:00:25,北京市-朝阳区,国贸CBD小屏组,0
B4E62D2FBC25,其他,2018-07-12 13:59:28,2018-07-12 13:59:28,2018-07-12 13:59:30,未知-未知,广播,2
B4FFFFFF404A,其他,2018-07-12 14:00:52,2018-07-12 14:00:52,2018-07-12 14:00:52,未知,僵尸屏,0
B4FFFFFF6F08,其他,2018-07-12 14:03:22,2018-07-12 14:03:22,2018-07-12 14:03:22,未知,僵尸屏,0
B5FFFFFF404A,其他,2018-07-12 14:03:03,2018-07-12 14:03:03,2018-07-12 14:03:03,北京市-海定区,中关村鼎好电子大厦屏,0
B5FFFFFF6F08,其他,2018-07-12 13:59:02,2018-07-12 13:59:02,2018-07-12 13:59:02,未知,僵尸屏,0
B60020E89DF1,其他,2018-07-12 14:04:55,2018-07-12 14:04:55,2018-07-12 14:04:55,未知-未知,广播,0
B608AEB91265,其他,2018-07-12 14:04:15,2018-07-12 14:04:15,2018-07-12 14:04:15,未知-未知,广播,0
B6876A19AAC7,其他,2018-07-12 14:04:40,2018-07-12 14:04:40,2018-07-12 14:04:40,未知-未知,广播,0
B6FFFFFF404A,其他,2018-07-12 13:59:58,2018-07-12 13:59:58,2018-07-12 13:59:58,未知,僵尸屏,0
B6FFFFFF6F08,其他,2018-07-12 13:58:56,2018-07-12 13:58:56,2018-07-12 13:59:45,北京市-海定区,中关村鼎好电子大厦屏,49
B7FFFFFF404A,其他,2018-07-12 13:59:35,2018-07-12 13:59:35,2018-07-12 13:59:35,北京市-海定区,中关村鼎好电子大厦屏,0
B7FFFFFF6F08,其他,2018-07-12 14:05:25,2018-07-12 14:05:25,2018-07-12 14:05:25,四川省-成都市,人民南路跳伞塔数码大厦屏,0
B8C1114A7B9B,其他,2018-07-12 23:56:44,2018-07-12 23:56:44,2018-07-12 23:56:44,四川省-成都市,人民南路跳伞塔数码大厦屏,6538
B8C1114A7B9B,其他,2018-07-12 20:06:43,2018-07-12 20:06:43,2018-07-12 20:20:40,四川省-成都市,人民南路跳伞塔数码大厦屏,837
B8C1114A7B9B,其他,2018-07-12 23:56:44,2018-07-12 23:56:44,2018-07-12 23:56:44,四川省-成都市,人民南路跳伞塔数码大厦屏,6538
B8E8563BA0A4,Apple,2018-07-12 13:59:49,2018-07-12 13:59:49,2018-07-12 14:04:24,北京市-朝阳区,国贸CBD小屏组,275
B8FFFFFF6F08,其他,2018-07-12 13:59:19,2018-07-12 13:59:19,2018-07-12 13:59:19,未知,僵尸屏,0
BAD91D9B892E,其他,2018-07-12 14:06:33,2018-07-12 14:06:33,2018-07-12 14:06:33,未知-未知,广播,0
BAFFFFFF404A,其他,2018-07-12 14:06:04,2018-07-12 14:06:04,2018-07-12 14:06:04,未知,僵尸屏,0
BAFFFFFF6F08,其他,2018-07-12 14:02:20,2018-07-12 14:02:20,2018-07-12 14:02:20,未知,僵尸屏,0
BC4CC49649F0,Apple,2018-07-12 14:03:11,2018-07-12 14:03:11,2018-07-12 14:05:26,未知-未知,广播,135
BC6C214D948A,Apple,2018-07-12 13:59:16,2018-07-12 13:59:16,2018-07-12 13:59:49,北京市-朝阳区,国贸CBD小屏组,33
C254A0267912,其他,2018-07-12 14:05:40,2018-07-12 14:05:40,2018-07-12 14:05:40,未知-未知,广播,0
C26FFEA03275,其他,2018-07-12 14:04:23,2018-07-12 14:04:23,2018-07-12 14:04:23,未知-未知,广播,0
C2FFFFFF6F08,其他,2018-07-12 14:03:16,2018-07-12 14:03:16,2018-07-12 14:04:06,北京市-海定区,中关村鼎好电子大厦屏,50
C3FFFFFF404A,其他,2018-07-12 14:02:26,2018-07-12 14:02:26,2018-07-12 14:02:26,北京市-朝阳区,国贸CBD小屏组,0
C3FFFFFF6F08,其他,2018-07-12 14:04:58,2018-07-12 14:04:58,2018-07-12 14:04:58,北京市-朝阳区,国贸CBD小屏组,0
C4346B7AE5C5,其他,2018-07-12 13:59:00,2018-07-12 13:59:00,2018-07-12 14:06:29,未知,僵尸屏,449
C5FFFFFF6F08,其他,2018-07-12 13:58:54,2018-07-12 13:58:54,2018-07-12 13:58:54,北京市-朝阳区,国贸CBD小屏组,0
C63136F22AA6,其他,2018-07-12 14:05:36,2018-07-12 14:05:36,2018-07-12 14:05:36,未知-未知,广播,0
C6FFFFFF404A,其他,2018-07-12 14:06:02,2018-07-12 14:06:02,2018-07-12 14:06:02,北京市-海定区,中关村鼎好电子大厦屏,0
C7FFFFFF404A,其他,2018-07-12 14:04:18,2018-07-12 14:04:18,2018-07-12 14:04:18,北京市-海定区,中关村鼎好电子大厦屏,0
C7FFFFFF6F08,其他,2018-07-12 14:05:10,2018-07-12 14:05:10,2018-07-12 14:05:10,北京市-海定区,中关村鼎好电子大厦屏,0
C80E7765F3DE,其他,2018-07-12 13:59:23,2018-07-12 13:59:23,2018-07-12 14:06:35,未知,僵尸屏,432
C80E7765F3E9,其他,2018-07-12 13:59:04,2018-07-12 13:59:04,2018-07-12 14:06:32,未知,僵尸屏,448
C85B76A9E207,其他,2018-07-12 13:59:39,2018-07-12 13:59:39,2018-07-12 14:06:35,未知,僵尸屏,416
C85B76A9ED5D,其他,2018-07-12 13:59:10,2018-07-12 13:59:10,2018-07-12 14:06:22,未知,僵尸屏,432
C8FFFFFF6F08,其他,2018-07-12 14:02:22,2018-07-12 14:02:22,2018-07-12 14:05:23,北京市-朝阳区,国贸CBD小屏组,181
C9FFFFFF404A,其他,2018-07-12 14:00:35,2018-07-12 14:00:35,2018-07-12 14:00:35,北京市-朝阳区,国贸CBD小屏组,0
CA2C809CC480,其他,2018-07-12 14:01:05,2018-07-12 14:01:05,2018-07-12 14:01:05,未知-未知,广播,0
CA9575682EEE,其他,2018-07-12 13:59:47,2018-07-12 13:59:47,2018-07-12 13:59:47,未知-未知,广播,0
CAFFFFFF404A,其他,2018-07-12 14:01:56,2018-07-12 14:01:56,2018-07-12 14:01:56,北京市-朝阳区,国贸CBD小屏组,0
CAFFFFFF6F08,其他,2018-07-12 14:05:49,2018-07-12 14:05:49,2018-07-12 14:05:49,北京市-海定区,中关村鼎好电子大厦屏,0
CC088D0B8247,Apple,2018-07-12 13:59:53,2018-07-12 13:59:53,2018-07-12 14:04:14,北京市-朝阳区,国贸CBD小屏组,261
CE224BA5A5D8,其他,2018-07-12 14:01:21,2018-07-12 14:01:21,2018-07-12 14:01:21,未知-未知,广播,0
CE25D465BD58,其他,2018-07-12 14:01:24,2018-07-12 14:01:24,2018-07-12 14:01:24,未知-未知,广播,0
CEE68CCD5713,其他,2018-07-12 14:05:06,2018-07-12 14:05:06,2018-07-12 14:05:06,未知-未知,广播,0
D02788617F64,HonHai,2018-07-12 13:58:59,2018-07-12 13:58:59,2018-07-12 14:06:44,未知,僵尸屏,465
D4258B6E5762,其他,2018-07-12 14:00:15,2018-07-12 14:00:15,2018-07-12 14:06:15,未知-未知,广播,360
D60FDD9C2F2D,其他,2018-07-12 13:59:29,2018-07-12 13:59:29,2018-07-12 13:59:29,未知-未知,广播,0
D692213AA736,其他,2018-07-12 14:00:33,2018-07-12 14:00:33,2018-07-12 14:00:33,未知-未知,广播,0
D88F768BDB6F,其他,2018-07-12 14:00:14,2018-07-12 14:00:14,2018-07-12 14:05:16,未知,僵尸屏,302
D89EF311752E,其他,2018-07-12 14:04:19,2018-07-12 14:04:19,2018-07-12 14:06:09,未知,僵尸屏,110
D89EF3117A01,其他,2018-07-12 14:04:38,2018-07-12 14:04:38,2018-07-12 14:04:38,未知,僵尸屏,0
DA3167F550FF,其他,2018-07-12 14:00:05,2018-07-12 14:00:05,2018-07-12 14:00:12,未知-未知,广播,7
DA4DDE7A8106,其他,2018-07-12 13:59:57,2018-07-12 13:59:57,2018-07-12 13:59:57,未知-未知,广播,0
DA6CBA578D69,其他,2018-07-12 14:02:19,2018-07-12 14:02:19,2018-07-12 14:02:19,未知-未知,广播,0
DA9855F72063,其他,2018-07-12 14:02:42,2018-07-12 14:02:42,2018-07-12 14:02:42,未知-未知,广播,0
DAA11905ECA0,其他,2018-07-12 14:04:16,2018-07-12 14:04:16,2018-07-12 14:04:16,未知-未知,广播,0
DAA11906DC90,其他,2018-07-12 14:01:19,2018-07-12 14:01:19,2018-07-12 14:01:19,未知-未知,广播,0
DAA11907EEA2,其他,2018-07-12 14:05:01,2018-07-12 14:05:01,2018-07-12 14:05:01,未知-未知,广播,0
DAA11909CE82,其他,2018-07-12 14:03:29,2018-07-12 14:03:29,2018-07-12 14:03:29,未知-未知,广播,0
DAA1190C8906,其他,2018-07-12 14:03:04,2018-07-12 14:03:04,2018-07-12 14:03:04,未知-未知,广播,0
DAA1190D3EF2,其他,2018-07-12 14:04:39,2018-07-12 14:04:39,2018-07-12 14:04:39,未知-未知,广播,0
DAA1190DA95E,其他,2018-07-12 14:00:01,2018-07-12 14:00:01,2018-07-12 14:00:01,未知-未知,广播,0
DAA1191DAB5F,其他,2018-07-12 14:02:41,2018-07-12 14:02:41,2018-07-12 14:02:41,未知-未知,广播,0
DAA1191DF3A7,其他,2018-07-12 14:01:19,2018-07-12 14:01:19,2018-07-12 14:01:19,未知-未知,广播,0
DAA119230CF7,其他,2018-07-12 14:02:16,2018-07-12 14:02:16,2018-07-12 14:02:16,未知-未知,广播,0
DAA119298034,其他,2018-07-12 14:03:15,2018-07-12 14:03:15,2018-07-12 14:03:15,未知-未知,广播,0
DAA1192A11C5,其他,2018-07-12 14:02:41,2018-07-12 14:02:41,2018-07-12 14:02:41,未知-未知,广播,0
DAA1192C9448,其他,2018-07-12 14:04:38,2018-07-12 14:04:38,2018-07-12 14:04:38,未知-未知,广播,0
DAA119303442,其他,2018-07-12 14:02:23,2018-07-12 14:02:23,2018-07-12 14:02:23,未知-未知,广播,0
DAA119312BDF,其他,2018-07-12 14:04:16,2018-07-12 14:04:16,2018-07-12 14:04:16,未知-未知,广播,0
DAA11937311C,其他,2018-07-12 14:03:15,2018-07-12 14:03:15,2018-07-12 14:03:15,未知-未知,广播,0
DAA11937E99D,其他,2018-07-12 13:59:39,2018-07-12 13:59:39,2018-07-12 13:59:39,未知-未知,广播,0
DAA11939B663,其他,2018-07-12 14:01:37,2018-07-12 14:01:37,2018-07-12 14:01:37,未知-未知,广播,0
DAA1193DBF63,其他,2018-07-12 14:01:52,2018-07-12 14:01:52,2018-07-12 14:01:52,未知-未知,广播,0
DAA119418D89,其他,2018-07-12 14:03:52,2018-07-12 14:03:52,2018-07-12 14:03:52,未知-未知,广播,0
DAA119451BCF,其他,2018-07-12 14:03:03,2018-07-12 14:03:03,2018-07-12 14:03:03,未知-未知,广播,0
DAA1194730E4,其他,2018-07-12 14:00:04,2018-07-12 14:00:04,2018-07-12 14:00:04,未知-未知,广播,0
DAA1194831E5,其他,2018-07-12 14:03:15,2018-07-12 14:03:15,2018-07-12 14:03:15,未知-未知,广播,0
DAA1194A31E5,其他,2018-07-12 14:02:41,2018-07-12 14:02:41,2018-07-12 14:02:41,未知-未知,广播,0
DAA1194FDD91,其他,2018-07-12 14:02:53,2018-07-12 14:02:53,2018-07-12 14:02:53,未知-未知,广播,0
DAA11954BCA7,其他,2018-07-12 14:00:28,2018-07-12 14:00:28,2018-07-12 14:00:28,未知-未知,广播,0
DAA11957CCFF,其他,2018-07-12 14:03:22,2018-07-12 14:03:22,2018-07-12 14:03:22,未知-未知,广播,0
DAA119585408,其他,2018-07-12 14:04:05,2018-07-12 14:04:05,2018-07-12 14:04:05,未知-未知,广播,0
DAA1195B570B,其他,2018-07-12 14:01:09,2018-07-12 14:01:09,2018-07-12 14:01:09,未知-未知,广播,0
DAA1195EB569,其他,2018-07-12 14:04:05,2018-07-12 14:04:05,2018-07-12 14:04:05,未知-未知,广播,0
DAA1195F13C7,其他,2018-07-12 14:01:09,2018-07-12 14:01:09,2018-07-12 14:01:09,未知-未知,广播,0
DAA119604934,其他,2018-07-12 13:59:39,2018-07-12 13:59:39,2018-07-12 13:59:39,未知-未知,广播,0
DAA11971ECA0,其他,2018-07-12 14:03:03,2018-07-12 14:03:03,2018-07-12 14:03:03,未知-未知,广播,0
DAA119755E49,其他,2018-07-12 14:01:19,2018-07-12 14:01:19,2018-07-12 14:01:19,未知-未知,广播,0
DAA11979F6AA,其他,2018-07-12 14:04:16,2018-07-12 14:04:16,2018-07-12 14:04:16,未知-未知,广播,0
DAA1197C782C,其他,2018-07-12 14:03:29,2018-07-12 14:03:29,2018-07-12 14:03:29,未知-未知,广播,0
DAA11981570B,其他,2018-07-12 14:05:01,2018-07-12 14:05:01,2018-07-12 14:05:01,未知-未知,广播,0
DAA1198B5455,其他,2018-07-12 14:02:07,2018-07-12 14:02:07,2018-07-12 14:02:07,未知-未知,广播,0
DAA1198E772B,其他,2018-07-12 14:00:16,2018-07-12 14:00:16,2018-07-12 14:00:16,未知-未知,广播,0
DAA1199042DF,其他,2018-07-12 14:03:30,2018-07-12 14:03:30,2018-07-12 14:03:30,未知-未知,广播,0
DAA11991301B,其他,2018-07-12 14:04:16,2018-07-12 14:04:16,2018-07-12 14:04:16,未知-未知,广播,0
DAA1199E01C9,其他,2018-07-12 14:00:24,2018-07-12 14:00:24,2018-07-12 14:00:25,未知-未知,广播,1
DAA119A05408,其他,2018-07-12 14:02:41,2018-07-12 14:02:41,2018-07-12 14:02:41,未知-未知,广播,0
DAA119A3792D,其他,2018-07-12 14:04:39,2018-07-12 14:04:39,2018-07-12 14:04:39,未知-未知,广播,0
DAA119A69A4E,其他,2018-07-12 14:02:52,2018-07-12 14:02:52,2018-07-12 14:02:52,未知-未知,广播,0
DAA119A9590D,其他,2018-07-12 14:04:39,2018-07-12 14:04:39,2018-07-12 14:04:39,未知-未知,广播,0
DAA119A9B66A,其他,2018-07-12 14:03:15,2018-07-12 14:03:15,2018-07-12 14:03:15,未知-未知,广播,0
DAA119ABDC90,其他,2018-07-12 14:04:39,2018-07-12 14:04:39,2018-07-12 14:04:39,未知-未知,广播,0
DAA119B76954,其他,2018-07-12 14:04:17,2018-07-12 14:04:17,2018-07-12 14:04:17,未知-未知,广播,0
DAA119BA6E22,其他,2018-07-12 14:03:15,2018-07-12 14:03:15,2018-07-12 14:03:15,未知-未知,广播,0
DAA119BC8135,其他,2018-07-12 14:04:05,2018-07-12 14:04:05,2018-07-12 14:04:05,未知-未知,广播,0
DAA119BD004F,其他,2018-07-12 14:00:17,2018-07-12 14:00:17,2018-07-12 14:00:17,未知-未知,广播,0
DAA119BD03EE,其他,2018-07-12 14:04:39,2018-07-12 14:04:39,2018-07-12 14:04:39,未知-未知,广播,0
DAA119BF50C4,其他,2018-07-12 14:02:39,2018-07-12 14:02:39,2018-07-12 14:02:39,未知-未知,广播,0
DAA119C0CDB8,其他,2018-07-12 14:01:19,2018-07-12 14:01:19,2018-07-12 14:01:19,未知-未知,广播,0
DAA119C1BD71,其他,2018-07-12 14:02:53,2018-07-12 14:02:53,2018-07-12 14:02:53,未知-未知,广播,0
DAA119C7EA99,其他,2018-07-12 14:03:37,2018-07-12 14:03:37,2018-07-12 14:03:38,未知-未知,广播,1
DAA119C86C24,其他,2018-07-12 14:01:02,2018-07-12 14:01:02,2018-07-12 14:01:02,未知-未知,广播,0
DAA119CEECA0,其他,2018-07-12 14:02:41,2018-07-12 14:02:41,2018-07-12 14:02:41,未知-未知,广播,0
DAA119D24D01,其他,2018-07-12 14:05:01,2018-07-12 14:05:01,2018-07-12 14:05:01,未知-未知,广播,0
DAA119DB32E6,其他,2018-07-12 14:02:53,2018-07-12 14:02:53,2018-07-12 14:02:53,未知-未知,广播,0
DAA119DE1822,其他,2018-07-12 14:02:22,2018-07-12 14:02:22,2018-07-12 14:02:22,未知-未知,广播,0
DAA119DEFEB2,其他,2018-07-12 13:59:39,2018-07-12 13:59:39,2018-07-12 13:59:39,未知-未知,广播,0
DAA119E29246,其他,2018-07-12 14:03:03,2018-07-12 14:03:03,2018-07-12 14:03:03,未知-未知,广播,0
DAA119E4CB7F,其他,2018-07-12 14:05:01,2018-07-12 14:05:01,2018-07-12 14:05:01,未知-未知,广播,0
DAA119E6984C,其他,2018-07-12 14:02:28,2018-07-12 14:02:28,2018-07-12 14:02:28,未知-未知,广播,0
DAA119E69A17,其他,2018-07-12 14:03:03,2018-07-12 14:03:03,2018-07-12 14:03:03,未知-未知,广播,0
DAA119E85004,其他,2018-07-12 14:05:01,2018-07-12 14:05:01,2018-07-12 14:05:01,未知-未知,广播,0
DAA119E9B964,其他,2018-07-12 14:00:02,2018-07-12 14:00:02,2018-07-12 14:00:03,未知-未知,广播,1
DAA119ED6A1E,其他,2018-07-12 14:05:01,2018-07-12 14:05:01,2018-07-12 14:05:01,未知-未知,广播,0
DAA119F111C5,其他,2018-07-12 13:59:39,2018-07-12 13:59:39,2018-07-12 13:59:39,未知-未知,广播,0
DAA119F3DA46,其他,2018-07-12 14:00:46,2018-07-12 14:00:46,2018-07-12 14:00:46,未知-未知,广播,0
DAA119F3EFA3,其他,2018-07-12 14:01:09,2018-07-12 14:01:09,2018-07-12 14:01:09,未知-未知,广播,0
DAA119F6D200,其他,2018-07-12 14:00:31,2018-07-12 14:00:31,2018-07-12 14:00:32,未知-未知,广播,1
DAA119F6DF93,其他,2018-07-12 14:00:16,2018-07-12 14:00:16,2018-07-12 14:00:16,未知-未知,广播,0
DAA119FE7089,其他,2018-07-12 14:01:21,2018-07-12 14:01:21,2018-07-12 14:01:22,未知-未知,广播,1
DC53604C66C0,其他,2018-07-12 13:59:16,2018-07-12 13:59:16,2018-07-12 14:06:33,未知-未知,广播,437
DE2E36FEE82E,其他,2018-07-12 14:00:38,2018-07-12 14:00:38,2018-07-12 14:00:56,未知-未知,广播,18
DEF19D23F154,其他,2018-07-12 14:02:38,2018-07-12 14:02:38,2018-07-12 14:02:38,未知-未知,广播,0
E006E6CE8F97,HonHai,2018-07-12 14:00:50,2018-07-12 14:00:50,2018-07-12 14:05:50,未知-未知,广播,300
E006E6CE9A0E,HonHai,2018-07-12 13:58:58,2018-07-12 13:58:58,2018-07-12 14:06:33,未知,僵尸屏,455
E6D5C72EA645,其他,2018-07-12 14:02:57,2018-07-12 14:02:57,2018-07-12 14:02:57,未知-未知,广播,0
E6F613C30991,其他,2018-07-12 13:59:05,2018-07-12 13:59:05,2018-07-12 13:59:05,未知-未知,广播,0
E8802EF16712,Apple,2018-07-12 14:01:12,2018-07-12 14:01:12,2018-07-12 14:05:36,未知,僵尸屏,264
E8B1FC4CA239,其他,2018-07-12 13:59:06,2018-07-12 13:59:06,2018-07-12 14:05:48,未知-未知,广播,402
E8B1FC5A879B,其他,2018-07-12 13:59:36,2018-07-12 13:59:36,2018-07-12 14:06:37,未知-未知,广播,421
E8B1FC5AC445,其他,2018-07-12 14:03:26,2018-07-12 14:03:26,2018-07-12 14:05:03,北京市-朝阳区,国贸CBD小屏组,97
E8B1FCAD1F4C,其他,2018-07-12 13:58:53,2018-07-12 13:58:53,2018-07-12 14:05:32,未知,僵尸屏,399
EAB17D786FE8,其他,2018-07-12 14:03:29,2018-07-12 14:03:29,2018-07-12 14:03:29,未知-未知,广播,0
EAD3476C79EA,其他,2018-07-12 14:03:22,2018-07-12 14:03:22,2018-07-12 14:03:22,未知-未知,广播,0
EAD6E82234FE,其他,2018-07-12 14:01:56,2018-07-12 14:01:56,2018-07-12 14:01:56,未知-未知,广播,0
EE170A33F722,其他,2018-07-12 14:01:48,2018-07-12 14:01:48,2018-07-12 14:01:48,未知-未知,广播,0
F0B4290F5DEC,Xiaomi,2018-07-12 14:01:56,2018-07-12 14:01:56,2018-07-12 14:04:32,未知,僵尸屏,156
F0B429CDDA00,Xiaomi,2018-07-12 13:59:14,2018-07-12 13:59:14,2018-07-12 14:06:33,未知,僵尸屏,439
F2231510B8B8,其他,2018-07-12 14:03:23,2018-07-12 14:03:23,2018-07-12 14:03:23,未知-未知,广播,0
F242B220867E,其他,2018-07-12 13:59:00,2018-07-12 13:59:00,2018-07-12 13:59:00,未知-未知,广播,0
F48C500B6F6C,其他,2018-07-12 13:59:38,2018-07-12 13:59:38,2018-07-12 14:01:37,未知,僵尸屏,119
F60F8D888CED,其他,2018-07-12 13:59:16,2018-07-12 13:59:16,2018-07-12 13:59:16,未知-未知,广播,0
F6E4DA3E9024,其他,2018-07-12 14:06:05,2018-07-12 14:06:05,2018-07-12 14:06:05,未知-未知,广播,0
F6F3B944683D,其他,2018-07-12 14:02:04,2018-07-12 14:02:04,2018-07-12 14:02:04,未知-未知,广播,0
FA5360AD50CC,其他,2018-07-12 14:02:15,2018-07-12 14:02:15,2018-07-12 14:02:15,未知-未知,广播,0
FA8B4C2A31A4,其他,2018-07-12 14:05:52,2018-07-12 14:05:52,2018-07-12 14:05:53,未知-未知,广播,1
FCD848C807B5,Apple,2018-07-12 14:00:41,2018-07-12 14:00:41,2018-07-12 14:06:32,北京市-朝阳区,国贸CBD小屏组,351
FE305C782386,其他,2018-07-12 14:02:50,2018-07-12 14:02:50,2018-07-12 14:02:50,未知-未知,广播,0
FE519EDD25D9,其他,2018-07-12 14:03:31,2018-07-12 14:03:31,2018-07-12 14:03:31,未知-未知,广播,0
FEC0608F30DC,其他,2018-07-12 14:02:51,2018-07-12 14:02:51,2018-07-12 14:02:51,未知-未知,广播,0
FEDE7582DCAF,其他,2018-07-12 14:01:12,2018-07-12 14:01:12,2018-07-12 14:01:12,未知-未知,广播,0
FEEBB23B99E6,其他,2018-07-12 14:01:56,2018-07-12 14:01:56,2018-07-12 14:01:56,未知-未知,广播,0

本次所有示例的函数有:

 // misc non-aggregate functions
expression[Abs]("abs"),
expression[Coalesce]("coalesce"),
expression[Explode]("explode"),
expression[Greatest]("greatest"),
expression[If]("if"),
expression[Inline]("inline"),
expression[IsNaN]("isnan"),
expression[IfNull]("ifnull"),
expression[IsNull]("isnull"),
expression[IsNotNull]("isnotnull"),
expression[Least]("least"),
expression[NaNvl]("nanvl"),
expression[NullIf]("nullif"),
expression[Nvl]("nvl"),
expression[Nvl2]("nvl2"),
expression[PosExplode]("posexplode"),
expression[Rand]("rand"),
expression[Randn]("randn"),
expression[Stack]("stack"),
expression[CaseWhen]("when"), // math functions
expression[Acos]("acos"),
expression[Asin]("asin"),
expression[Atan]("atan"),
expression[Atan2]("atan2"),
expression[Bin]("bin"),
expression[BRound]("bround"),
expression[Cbrt]("cbrt"),
expression[Ceil]("ceil"),
expression[Ceil]("ceiling"),
expression[Cos]("cos"),
expression[Cosh]("cosh"),
expression[Conv]("conv"),
expression[ToDegrees]("degrees"),
expression[EulerNumber]("e"),
expression[Exp]("exp"),
expression[Expm1]("expm1"),
expression[Floor]("floor"),
expression[Factorial]("factorial"),
expression[Hex]("hex"),
expression[Hypot]("hypot"),
expression[Logarithm]("log"),
expression[Log10]("log10"),
expression[Log1p]("log1p"),
expression[Log2]("log2"),
expression[Log]("ln"),
expression[UnaryMinus]("negative"),
expression[Pi]("pi"),
expression[Pmod]("pmod"),
expression[UnaryPositive]("positive"),
expression[Pow]("pow"),
expression[Pow]("power"),
expression[ToRadians]("radians"),
expression[Rint]("rint"),
expression[Round]("round"),
expression[ShiftLeft]("shiftleft"),
expression[ShiftRight]("shiftright"),
expression[ShiftRightUnsigned]("shiftrightunsigned"),
expression[Signum]("sign"),
expression[Signum]("signum"),
expression[Sin]("sin"),
expression[Sinh]("sinh"),
expression[StringToMap]("str_to_map"),
expression[Sqrt]("sqrt"),
expression[Tan]("tan"),
expression[Tanh]("tanh"), expression[Add]("+"),
expression[Subtract]("-"),
expression[Multiply]("*"),
expression[Divide]("/"),
expression[Remainder]("%"), // aggregate functions
expression[HyperLogLogPlusPlus]("approx_count_distinct"),
expression[Average]("avg"),
expression[Corr]("corr"),
expression[Count]("count"),
expression[CovPopulation]("covar_pop"),
expression[CovSample]("covar_samp"),
expression[First]("first"),
expression[First]("first_value"),
expression[Kurtosis]("kurtosis"),
expression[Last]("last"),
expression[Last]("last_value"),
expression[Max]("max"),
expression[Average]("mean"),
expression[Min]("min"),
expression[Percentile]("percentile"),
expression[Skewness]("skewness"),
expression[ApproximatePercentile]("percentile_approx"),
expression[ApproximatePercentile]("approx_percentile"),
expression[StddevSamp]("std"),
expression[StddevSamp]("stddev"),
expression[StddevPop]("stddev_pop"),
expression[StddevSamp]("stddev_samp"),
expression[Sum]("sum"),
expression[VarianceSamp]("variance"),
expression[VariancePop]("var_pop"),
expression[VarianceSamp]("var_samp"),
expression[CollectList]("collect_list"),
expression[CollectSet]("collect_set"), // string functions
expression[Ascii]("ascii"),
expression[Base64]("base64"),
expression[Concat]("concat"),
expression[ConcatWs]("concat_ws"),
expression[Decode]("decode"),
expression[Elt]("elt"),
expression[Encode]("encode"),
expression[FindInSet]("find_in_set"),
expression[FormatNumber]("format_number"),
expression[FormatString]("format_string"),
expression[GetJsonObject]("get_json_object"),
expression[InitCap]("initcap"),
expression[StringInstr]("instr"),
expression[Lower]("lcase"),
expression[Length]("length"),
expression[Levenshtein]("levenshtein"),
expression[Like]("like"),
expression[Lower]("lower"),
expression[StringLocate]("locate"),
expression[StringLPad]("lpad"),
expression[StringTrimLeft]("ltrim"),
expression[JsonTuple]("json_tuple"),
expression[ParseUrl]("parse_url"),
expression[FormatString]("printf"),
expression[RegExpExtract]("regexp_extract"),
expression[RegExpReplace]("regexp_replace"),
expression[StringRepeat]("repeat"),
expression[StringReverse]("reverse"),
expression[RLike]("rlike"),
expression[StringRPad]("rpad"),
expression[StringTrimRight]("rtrim"),
expression[Sentences]("sentences"),
expression[SoundEx]("soundex"),
expression[StringSpace]("space"),
expression[StringSplit]("split"),
expression[Substring]("substr"),
expression[Substring]("substring"),
expression[SubstringIndex]("substring_index"),
expression[StringTranslate]("translate"),
expression[StringTrim]("trim"),
expression[Upper]("ucase"),
expression[UnBase64]("unbase64"),
expression[Unhex]("unhex"),
expression[Upper]("upper"),
expression[XPathList]("xpath"),
expression[XPathBoolean]("xpath_boolean"),
expression[XPathDouble]("xpath_double"),
expression[XPathDouble]("xpath_number"),
expression[XPathFloat]("xpath_float"),
expression[XPathInt]("xpath_int"),
expression[XPathLong]("xpath_long"),
expression[XPathShort]("xpath_short"),
expression[XPathString]("xpath_string"), // datetime functions
expression[AddMonths]("add_months"),
expression[CurrentDate]("current_date"),
expression[CurrentTimestamp]("current_timestamp"),
expression[DateDiff]("datediff"),
expression[DateAdd]("date_add"),
expression[DateFormatClass]("date_format"),
expression[DateSub]("date_sub"),
expression[DayOfMonth]("day"),
expression[DayOfYear]("dayofyear"),
expression[DayOfMonth]("dayofmonth"),
expression[FromUnixTime]("from_unixtime"),
expression[FromUTCTimestamp]("from_utc_timestamp"),
expression[Hour]("hour"),
expression[LastDay]("last_day"),
expression[Minute]("minute"),
expression[Month]("month"),
expression[MonthsBetween]("months_between"),
expression[NextDay]("next_day"),
expression[CurrentTimestamp]("now"),
expression[Quarter]("quarter"),
expression[Second]("second"),
expression[ToDate]("to_date"),
expression[ToUnixTimestamp]("to_unix_timestamp"),
expression[ToUTCTimestamp]("to_utc_timestamp"),
expression[TruncDate]("trunc"),
expression[UnixTimestamp]("unix_timestamp"),
expression[WeekOfYear]("weekofyear"),
expression[Year]("year"),
expression[TimeWindow]("window"), // collection functions
expression[CreateArray]("array"),
expression[ArrayContains]("array_contains"),
expression[CreateMap]("map"),
expression[CreateNamedStruct]("named_struct"),
expression[MapKeys]("map_keys"),
expression[MapValues]("map_values"),
expression[Size]("size"),
expression[SortArray]("sort_array"),
CreateStruct.registryEntry, // misc functions
expression[AssertTrue]("assert_true"),
expression[Crc32]("crc32"),
expression[Md5]("md5"),
expression[Murmur3Hash]("hash"),
expression[Sha1]("sha"),
expression[Sha1]("sha1"),
expression[Sha2]("sha2"),
expression[SparkPartitionID]("spark_partition_id"),
expression[InputFileName]("input_file_name"),
expression[MonotonicallyIncreasingID]("monotonically_increasing_id"),
expression[CurrentDatabase]("current_database"),
expression[CallMethodViaReflection]("reflect"),
expression[CallMethodViaReflection]("java_method"), // grouping sets
expression[Cube]("cube"),
expression[Rollup]("rollup"),
expression[Grouping]("grouping"),
expression[GroupingID]("grouping_id"), // window functions
expression[Lead]("lead"),
expression[Lag]("lag"),
expression[RowNumber]("row_number"),
expression[CumeDist]("cume_dist"),
expression[NTile]("ntile"),
expression[Rank]("rank"),
expression[DenseRank]("dense_rank"),
expression[PercentRank]("percent_rank"), // predicates
expression[And]("and"),
expression[In]("in"),
expression[Not]("not"),
expression[Or]("or"), // comparison operators
expression[EqualNullSafe]("<=>"),
expression[EqualTo]("="),
expression[EqualTo]("=="),
expression[GreaterThan](">"),
expression[GreaterThanOrEqual](">="),
expression[LessThan]("<"),
expression[LessThanOrEqual]("<="),
expression[Not]("!"), // bitwise
expression[BitwiseAnd]("&"),
expression[BitwiseNot]("~"),
expression[BitwiseOr]("|"),
expression[BitwiseXor]("^"), // Cast aliases (SPARK-16730)
castAlias("boolean", BooleanType),
castAlias("tinyint", ByteType),
castAlias("smallint", ShortType),
castAlias("int", IntegerType),
castAlias("bigint", LongType),
castAlias("float", FloatType),
castAlias("double", DoubleType),
castAlias("decimal", DecimalType.USER_DEFAULT),
castAlias("date", DateType),
castAlias("timestamp", TimestampType),
castAlias("binary", BinaryType),
castAlias("string", StringType)

Start

import org.apache.spark.sql
import org.apache.spark.sql.functions._ object Query {
val ssc = new sql.SparkSession
.Builder()
.appName("query")
.master("local[2]")
.getOrCreate()
ssc.conf.set("spark.sql.shuffle.partitions", 6)
ssc.conf.set("spark.executor.memory", "6g") ssc.sparkContext.setLogLevel("error")
def main(args:Array[String]):Unit = {
//读入文件并将其转换为DF
val fileName = "file:///E:\\工作\\WifiData\\data\\" + args(0) + "visit.txt"
val df_1 = ssc.read.option("headler", "false").option("inferschema", "true").csv(fileName)
.toDF("mac", "phone_brand", "enter_time", "first_time", "last_time", "region", "screen", "stay_long")
}

注:在使用函数的时候最好要导入org.apache.spark.sql.functions._这个包

  import ssc.implicits._(这个包在实际写sql的时候将其导入)

一、混合非聚合函数(misc non-aggregate functions)

  1、abs:绝对值 

import ssc.implicits._
df_1.selectExpr("abs(stay_long) as res_abs").show(5)

    Sparksql的内置函数的使用以及案例

  2、coalesce:返回第一列部位空的列值,源码解释如下:

/**Returns the first column that is not null, or null if all inputs are null.
* For example, `coalesce(a, b, c)` will return a if a is not null,
* or b if a is null and b is not null, or c if both a and b are null but c is not null.
import ssc.implicits._
df_1.selectExpr("coalesce(mac,screen,stay_long) as res_colesce").show(5)//手动将第一条记录的第一个字段置为空,则显示第二个字段值 

   Sparksql的内置函数的使用以及案例

  3、explode:Creates a new row for each element in the given array or map column.

  3.1、有些时候我们需要使用sparksql来解析一些json文件,对于常规的无嵌套的json文件来说,比如:

{"name":"Michael"}
{"name":"Andy","age":30}
{"name":"Justin","age":19}

  

val fileName = "E:\\工作\\WifiData\\data\\" + startDate + "visit.txt"
val df_numFile = ssc.read.json("file:///" + fileName)
df_numFile.select("*").show()

  

  Sparksql的内置函数的使用以及案例

  我们直接使用ssc.read.json("path")的方式进行读取,之后可以直接进行相关的sql进行查询。

  但是对于嵌套类型的json文件,就感觉到有点吃力了,比如:

{"name":"Michael", "age":25,"myScore":[{"score1":19,"score2":23},{"score1":58,"score2":50}]}
{"name":"Andy", "age":30,"myScore":[{"score1":29,"score2":33},{"score1":38,"score2":52},{"score1":88,"score2":71}]}
{"name":"Justin", "age":19,"myScore":[{"score1":39,"score2":43},{"score1":28,"score2":53}]}

  

val fileName = "E:\\工作\\WifiData\\data\\" + startDate + "visit.txt"
val df_numFile = ssc.read.json("file:///" + fileName)
df_numFile.select("*").show()

  Sparksql的内置函数的使用以及案例

然而,这并不是我们想要的,这里explode函数有有用处了,将上面的代码修改一下

val fileName = "E:\\工作\\WifiData\\data\\" + startDate + "visit.txt"
val df_numFile = ssc.read.json("file:///" + fileName) val df_score = df_numFile.select(df_numFile("name"),explode(df_numFile("myScore"))).toDF("name","myScore")
val dfMyScore = df_score.select("name","myScore.score1","myScore.score2") 

  Sparksql的内置函数的使用以及案例

咦?好像少点啥,age字段呢,给丢了,加上,一个都不能少不是:

val fileName = "E:\\工作\\WifiData\\data\\" + startDate + "visit.txt"
val df_numFile = ssc.read.json("file:///" + fileName) val df_score = df_numFile.select(df_numFile("name"),df_numFile("age"),explode(df_numFile("myScore"))).toDF("name","myScore")
val dfMyScore = df_score.select("name","age","myScore.score1","myScore.score2")
dfMyScore.select("*").show()
//这会应该全了吧,试试...

  Sparksql的内置函数的使用以及案例

什么情况?报错了,列名数量不匹配,在来修改

val fileName = "E:\\工作\\WifiData\\data\\" + startDate + "visit.txt"
val df_numFile = ssc.read.json("file:///" + fileName) val df_score = df_numFile.select(df_numFile("name"),df_numFile("age"),explode(df_numFile("myScore"))).toDF("name","age","myScore")
val dfMyScore = df_score.select("name","age","myScore.score1","myScore.score2")
dfMyScore.select("*").show()
//再试一下...

  Sparksql的内置函数的使用以及案例

这会得到了正确的结果。

  3.2、explode还可以将单列扩展成多行

  参数可以是Array,可以是Map 

ssc.sql(
"""
|select explode(Array("a","b","c","d"))
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select explode(Map("a","b"))
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

总结:explode该函数将指定 字段转换成DF的时候,是对应的整个表的列而不是该函数里面的字段对应里面的列数,这句话听起来比较绕口,把我自己都绕蒙了,还是得根据实例来理解

  4、greatest:求列表中的最大值

源码解释:(Returns the greatest value of the list of values, skipping null values.This function takes at least 2 parameters. It will return null iff all parameters are null.)

注意:必须是同类型的才可以比较

df_1.select(greatest("enter_time","first_time","last_time") as("greatest")).show(3)

   Sparksql的内置函数的使用以及案例

   5、if:用于条件判断

源码解释:Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select enter_time,first_time,last_time,if(stay_long = 0,'x','y') as type from d1
""".stripMargin).show(3)

  Sparksql的内置函数的使用以及案例

  6、inline:

   7、isnan:判断字段值是否为空

 df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select isnan(screen) as isnull,first_time,last_time,if(isnan(mac),1,2) as type from d1
""".stripMargin).show(3)

  Sparksql的内置函数的使用以及案例

  8、json_tuple():获取json中指定字段的值  

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,json_tuple('{"a":"lihua","b":"wangming"}','a','b')
|from d1
|
""".stripMargin).show(4, false)

  如果需要对查询出的结果起别名,可以使用嵌套外层循环即可

  Sparksql的内置函数的使用以及案例

  9、get_json_object():获取指定json路径的指定字符串的值,使用$.的方式 

  [JSON Path介绍](http://blog.csdn.net/koflance/article/details/63262484)

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,get_json_object('{"a":"lihua","b":"wangming"}','$.a') as valueOfJson
|from d1
|
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  10、from_json:解析json字符串为StructType或ArrayType

  

二、字符函数

  1、ascii(string str):返回str中第一个字符的ascii值

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,ascii(mac) as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  2、base64(column str):将str列进行base64编码作为字符串返回,与unbase64对应

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,base64(mac) as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  3、concat(string str1,string str2...):将多个字符串拼接为一个字符串

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,concat(mac,first_time) as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  4、concat_ws:使用指定的连接符将多个字符拼接为一个字符

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,first_time,concat_ws('-',mac,first_time) as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  5、encode(value:column,charset:string):转码,character支持的格式有:US-ASCII,ISO-8859-1,UTF-8,UTF-16BE,UTF-16LE,UTF-16

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,first_time,encode(mac,'ISO-8859-1') as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  6、decode(value:column,charset:string):使用charset编码格式进行解码,支持的格式有:US-ASCII,ISO-8859-1,UTF-8,UTF-16BE,UTF-16LE,UTF-16

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,first_time,decode(encode(mac,'iso-8859-1'),'iso-8859-1') as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  7、format_number(value:column,Int:d):实现对数字进行格式化,例如四舍五入保留d位小数,column必须是数组,不能是字符串

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,format_number(3.1415926,3) as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  8、format_string(format:string,argument:column*):将column按照format进行格式化

   目前我还不知道有么用处

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,format_string('%s',mac) as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  9、get_json_object():

  10、initcap(column:str):将str字段的首个字母大写

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,initcap('asdfSDFcasdfASDf') as after_convert
      |from d1
     """.stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  11、lower,upper:转大写,转小写 

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,lower(mac) as lower,upper(mac) as upper
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  12、instr(str:column,substring:String):返回substring在str中第一次出现的位置(索引从1开始)

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,instr(mac,'D') as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

 13、length:返回字符串的长度

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,length(mac) as after_convert
|from d1
""".stripMargin).show(3, false)

   Sparksql的内置函数的使用以及案例

  14、 levenshtein(l:column,r:column):计算两个字符串之间的编辑距离 

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,first_time,levenshtein(mac,first_time) as after_convert
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  15、locate(substr:string,str:column,pos:Int):

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,first_time,locate('E',mac) as after_convert_1,locate('D',mac,8) as after_convert_2
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  16、lpad:左补齐

    rpad:右补齐    

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,lpad(mac,20,'--') as left,rpad(mac,20,'--') as right
|from d1
""".stripMargin).show(3, false)

  Sparksql的内置函数的使用以及案例

  17、ltrim、ltrim、trim:去除做空格,去除有空格,去除左右两边的空格  

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,ltrim(mac) as ltrim,trim(mac) as rtrim,trim(mac) as trim
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  18、json_tuple():

  19、parse_url(string url,string par):按照par规则抽取url中的字段 

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select mac,'http://facebook.com/path/p1.php?query=1#Ref' as url,
|parse_url('http://facebook.com/path/p1.php?query=1#Ref','HOST') as host,
|parse_url('http://facebook.com/path/p1.php?query=1#Ref','PATH') as path,
|parse_url('http://facebook.com/path/p1.php?query=1#Ref','QUERY') as query,
|parse_url('http://facebook.com/path/p1.php?query=1#Ref','PROTOCOL') as protocol,
|parse_url('http://facebook.com/path/p1.php?query=1#引用','REF') as ref
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  20、repeat(column str,int n):将str重复n次

    reverse(column str):将str反转打印 

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,reverse(mac) as reverse,repeat(mac,) as repeat_mac
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  21、regextp_extract(column:col,exp:string,groupId:int):正则提取匹配的组

  

  22、soundex (column:col):计算桑迪克斯代码(soundex code)PS:用于按英语发音来索引姓名,发音相同但拼写不同的单词,会映射成同一个码。 

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,(first_time) as soundex
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  23、rlike:可以通过正则进行where条件匹配 

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,screen
|from d1 where screen rlike '僵.*'
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  24、sentence():

  25、split(column:col,string:str):按照str作为分隔符对col进行分割

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,screen,split(first_time,' ')[] as split
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  26、substr(column:col,start:int,end:int):截取col中[start,end]之间的字符

    substr(column:col,stat:int):截取start开始到结尾的字符

    substring(column:col,pos:int,len:int):从col的第pos个字符开始截取长度为len个字符,注意,索引从1开始 

    substring_index(column:col,delimit:string,count:int):对col使用delimit进行分割,返回分割后count个元素,如果count>0,则从左->右开始算起,如果count<0 则从右—>左开始算起,并拼接起来   

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,
|substr(first_time,) as sunstr_1,
|substr(first_time,,) as substr_2,
|substring(first_time,,) as substring,
      |substring_index('http://facebook.com/path/p1.php?query=1#Ref','/','3')
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  27、translate(src:string,match:string,replace:string):将src中的match全部替换为replace 

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,
|translate(first_time,'','') as translate
|from d1
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  28、

三、日期函数

  1、add_months:将指定时间字段的月份加指定的月份数

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
  |select first_time,add_months(first_time,3) as after_add from d1
""".stripMargin).show(3)

  Sparksql的内置函数的使用以及案例

  2、current_date:获取当前的日期

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,current_date() as current from d1
""".stripMargin).show(3)

  Sparksql的内置函数的使用以及案例

  3、current_timestamp:获取当前的时间

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,current_timestamp() as current_time from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  4、datediff(a,b):返回日期a与日期b的时间差,结果是天数

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,current_timestamp() as current_time,datediff(first_time,current_timestamp) as diff from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  5、date_add:指定天数之后的某几天

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,date_add(first_time,3) as after_add from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  6、date_format:将日期字段(timestamp类型)格式化为想要的格式

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,
|date_format(first_time,'yyyy-MM-dd') as date,
|date_format(first_time,'yyyy') as year,
|date_format(first_time,'MM') as month,
|date_format(first_time,'dd') as day,
|date_format(first_time,'HH:mm:ss') as time,
|date_format(first_time,'HH') as hour,
|date_format(first_time,'mm') as minute,
|date_format(first_time,'ss') as seconds
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  7、date_sub:返回日期的前几天

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,date_sub(first_time,3) as sub
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  8、day:返回日期中的天

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,day(first_time) as sub
|from d1
""".stripMargin).show(3,false)
}

  Sparksql的内置函数的使用以及案例

  9、dayofyear:返回给定时间中day是这一年中的第几天,返回一个数字

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,dayofyear(first_time) as day
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  10、dayofmonth:返回给定时间中的天是该月的第几天

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,dayofmonth(first_time) as month
|from d1
""".stripMargin).show(3,false)

 Sparksql的内置函数的使用以及案例

  11、from_unixtime:

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,from_unixtime(tm,'yyyy-MM-dd HH:mm:ss') as after_convert
|from d1
""".stripMargin).show(3,false)

  注:这里的tm必须是时间秒数,例如(1250111000)

Sparksql的内置函数的使用以及案例

  12、from_utc_timestamp :如果给定的时间戳并非UTC,则将其转化成指定时区下的时间戳

 df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,from_utc_timestamp(first_time,'PST') as after_convert //将first_time(默认是UTC时区时间)转换为PST时区的时间戳
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  13、hour:返回指定时间戳的小时

 df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,hour(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  14、last_day:返回给定时间戳的当月的最后一天的日期

 df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,last_day(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  15、minute:返回指定时间戳的分钟数

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,minute(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  16、month:返回指定时间戳的月

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,month(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

   17、months_between:返回两个时间戳月份的时间差

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select enter_time,first_time,months_between(enter_time,first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  18、next_day:返回指定时间戳的一周之后的这天

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,next_day(first_time,,'TU') as after_convert //返回first_time开始,下周的星期二的日期
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  19、now:放回当前的时间戳

  

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,now() as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  20、quarter:返回指定时间戳是在该年中的第几个季度

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,quarter(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  21、second:返回给定时间戳的秒数

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,second(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  22、to_date:返回时间字符串的日期部分

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,to_date(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  23、to_unix_timestamp:根据提供的字符串格式转换成Unix时间

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,to_unix_timestamp('16/Mar/2017:12:25:01 +0800','dd/MMM/yyyy:HH:mm:ss Z') as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,to_unix_timestamp(first_time,'yyyy-MM-dd HH:mm:ss') as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  24、to_utc_timestamp:将指定的时间戳转换为UTC下的时间戳

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,to_utc_timestamp(first_time,'yyyy-MM-dd HH:mm:ss') as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  25、trunc:返回指定时间最开始的年份或是月份(只支持MONTH/MON/MM,YEAR/YYYY/YY)

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,trunc(first_time,'MM') as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  26、unix_timestamp:转换时间字符串格式yyyy-MM-dd HH:mm:dd 到unix时间(秒),如果不加参数的话,则获取的是当前的时间的unix时间

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,unix_timestamp(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  27、weekofyear:返回指定的时间戳是该年中第几周

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,weekofyear(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  28、year:返回指定时间戳中的年

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select first_time,year(first_time) as after_convert
|from d1
""".stripMargin).show(3,false)

  Sparksql的内置函数的使用以及案例

  29、window:

四、聚合函数(尽量结合分组函数group/groupBy使用)

  1、count():求总条数

    avg():求平均值

    sum():累加求和

    mean():求平均值

    max():求最大值

    min():求最小值    

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select phone_brand,
|count(phone_brand) as count,
|avg(stay_long) as avg,
|mean(stay_long) as mean,
|min(stay_long) as min,
|max(stay_long) as max,
|sum(stay_long) as sum
|from d1 group by phone_brand
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

  2、var_pop:求总体方差(方差:) 

     stddev_pop:求总体的标准差(标准差:方差开方既得)

     skenwness:偏度

     kurtosis:峰态值

df_1.createOrReplaceTempView("d1")
ssc.sql(
"""
|select phone_brand,
|var_pop(stay_long) as fc,
|stddev_pop(stay_long) as bzc,
|skewness(stay_long) as pd,
|kurtosis(stay_long) as ftz
|from d1 group by phone_brand
""".stripMargin).show(4, false)

  Sparksql的内置函数的使用以及案例

五、数学函数

六、类型转换函数

  

 

  

上一篇:从shell(终端)中退出python


下一篇:jQuery选择器--:first和:last