1.背景
在设计用户标签时,需要将1个维度多个值的情况生成多个标签,例如,用户视频的设备的类型,
由于用户含有多个设备,所以需要将每个设备类型设置成一个标签,业务方在使用时,就可以方便的 与
或
逻辑过滤。例如下面数据:
+------------+--------------------+-----+-----+
| proxyId| hc| type| flow|
+------------+--------------------+-----+-----+
|000B2F6F9AAD|16a204da44b073cc4...| 0|18561|
|000B2F6F9AAD|c54d6d0ca0a25b06e...| 0| 594|
|000B2F6F9AAD|ff45bdcf521cc573e...| 0| 236|
|000B2F6F9AAD|bfa73c6c25a164b45...|20090| 0|
|000B2F6F9AAD|1e773e8eb32445bfd...|20090| 0|
|000B2F6F9AAD|215baeb631025228e...| 0| 206|
|000B2F6F9AAD|8f4c7290efa0a8e40...| 0| 1013|
|000B2F6F9AAD|c50a2ad7e0903f3c8...| 0| 1076|
|000B2F6F9AAD|0d3051d5205521007...| 2| 2|
|000B2F6F9AAD|7b6a77f16828ab893...| 0| 84|
|000B2F6F9AAD|09a32d812224e5ffe...|20090| 0|
|000B2F6F9AAD|26a6c8827780a2a91...|20085| 0|
|000B2F6F9AAD|34fc93529a4c9475e...| 0| 813|
|000B2F6F9AAD|ddc0204e51308b5e4...| 0|10901|
|000B2F6F9AAD|7794bf8e79fef8613...| 0| 347|
|000B2F6F9AAD|0ba18998322603234...| 0| 457|
|000B2F6F9AAD|aa3ed70c7a7027fc4...|25451| 0|
|000B2F6F9AAD|bf21bce2d9b59169e...|25451| 0|
|000B2F6F9AAD|1a6b8932e61fea61c...| 0| 208|
|000B2F6F9AAD|5fa30a58f8a1c7cbb...|20090| 0|
+------------+--------------------+-----+-----+
2.使用方法
pivot
sql语法函数在spark 2.4版本才被支持,但是在spark官网的spark2.4 SQL api的文档没有发现使用的说明!- -
使用方式如下:
spark.sql("select * from temp PIVOT(count(r) FOR r IN (0,20085,20084))").show()
+------------+--------------------+-----+----+-----+-----+
| proxyId| hc| flow| 0|20085|20084|
+------------+--------------------+-----+----+-----+-----+
|000B2F6F9AAD|16a204da44b073cc4...|18561| 1| null| null|
|000B2F6F9AAD|c54d6d0ca0a25b06e...| 594| 1| null| null|
|000B2F6F9AAD|ff45bdcf521cc573e...| 236| 1| null| null|
|000B2F6F9AAD|bfa73c6c25a164b45...| 0|null| null| null|
|000B2F6F9AAD|1e773e8eb32445bfd...| 0|null| null| null|
|000B2F6F9AAD|215baeb631025228e...| 206| 1| null| null|
|000B2F6F9AAD|8f4c7290efa0a8e40...| 1013| 1| null| null|
|000B2F6F9AAD|c50a2ad7e0903f3c8...| 1076| 1| null| null|
|000B2F6F9AAD|0d3051d5205521007...| 2|null| null| null|
|000B2F6F9AAD|7b6a77f16828ab893...| 84| 1| null| null|
|000B2F6F9AAD|09a32d812224e5ffe...| 0|null| null| null|
|000B2F6F9AAD|26a6c8827780a2a91...| 0|null| 1| null|
|000B2F6F9AAD|34fc93529a4c9475e...| 813| 1| null| null|
|000B2F6F9AAD|ddc0204e51308b5e4...|10901| 1| null| null|
|000B2F6F9AAD|7794bf8e79fef8613...| 347| 1| null| null|
|000B2F6F9AAD|0ba18998322603234...| 457| 1| null| null|
|000B2F6F9AAD|aa3ed70c7a7027fc4...| 0|null| null| null|
|000B2F6F9AAD|bf21bce2d9b59169e...| 0|null| null| null|
|000B2F6F9AAD|1a6b8932e61fea61c...| 208| 1| null| null|
|000B2F6F9AAD|5fa30a58f8a1c7cbb...| 0|null| null| null|
+------------+--------------------+-----+----+-----+-----+
我尝试这样使用spark.sql("select * from temp PIVOT(count(r) FOR r )").show()
提示语法错误!如果列有很多值,并且后续业务扩展,也会增加很多,还需要维护 in
后面的值,这就比较尴尬了。
但是使用算子是可以的,只能通过编码实现。不能使用sql配置的方式, - -
df.groupBy("hc").pivot("r").agg(count("r")).show()
+--------------------+----+----+---+----+---+---+---+---+---+---+---+---+---+---+----+----+-----+
| hc|-101|-100| -2| 0| 8| 15| 21| 23| 24| 25| 27| 85| 86| 90|1000|1032|20001|
+--------------------+----+----+---+----+---+---+---+---+---+---+---+---+---+---+----+----+-----+
|5de14e6e009d273a1...| 0| 0| 0| 12| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|a6fd3c214bb7d393e...| 140| 2| 0|1328| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|952a8ee68398a8742...| 0| 0| 3| 10| 0| 0| 0| 0| 5| 0| 0| 0| 0| 0| 0| 0| 0|
|aca5820a94ce68b74...| 0| 0| 0| 2| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|6701bc0b85e8a8ac7...| 0| 0| 0| 5| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|5dabb7d9d526d60c3...| 0| 0| 1| 1| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|03d8fa9c5f69ffce6...| 0| 0| 0| 13| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|a38775a8a7dba796d...| 0| 0| 0| 56| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|95baf775c11f138e2...| 0| 0| 0| 9| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|408cd837a780d3511...| 0| 0| 0| 25| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|dbea0bc55f633bd74...| 0| 0| 0| 16| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|53e8c58098f78d082...| 0| 0| 6| 25| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|df97b94275b68ce6b...| 1| 1| 0|1846| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|1e4e8ab81084dc0ea...| 0| 0| 0| 4| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|0648970099ce3836d...| 0| 0| 0| 21| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|244c01d10b63df00a...| 0| 0| 0| 2| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|ea66db7adabf05e12...| 46| 0| 0|1325| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|de982164e312e43c0...| 0| 0| 0| 7| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|3a795daf26b0deb36...| 0| 0| 0| 7| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
|6cad16d90c42dfb71...| 0| 0| 0| 11| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0| 0|
+--------------------+----+----+---+----+---+---+---+---+---+---+---+---+---+---+----+----+-----+