mysql collect_set_hive：数据库“行专列”操作---使用collect_set/collect_list/collect_all & row_number()over(partit...

selectgridid,height,collect_set(cell),collect_set(mrcount),collect_set(weakmrcount)from (select * from tommyduan_test order by gridid,height,mrcount desc) t10group bygridid,height;+---------+---------+-------------------------------------------------------------+----------------------+------+--+

| gridid | height | _c2 | _c3 | _c4 |

+---------+---------+-------------------------------------------------------------+----------------------+------+--+

| g1 | 1 | ["cell3","cell2","cell19","cell1","cell6","cell5","cell4"] | [23,22,21,12,4,3,1] | [3] |

| g2 | 1 | ["cell19","cell6","cell5","cell4"] | [21,4,3,1] | [3] |

+---------+---------+-------------------------------------------------------------+----------------------+------+--+

selectgridid,height,collect_set(cell) cellArray,collect_set(mrcount) mrcountArray,collect_set(weakmrcount) weakmrcountArrayfrom(select gridid,height,cell,mrcount,weakmrcount,row_number()over(partition by gridid,height order by mrcount desc) rnfromtommyduan_testgroup bygridid,height,cell,mrcount,weakmrcount

) t10where rn<4

group bygridid,height;+---------+---------+-----------------------------+---------------+-------------------+--+

| gridid | height | cellarray | mrcountarray | weakmrcountarray |

+---------+---------+-----------------------------+---------------+-------------------+--+

| g1 | 1 | ["cell3","cell2","cell19"] | [23,22,21] | [3] |

| g2 | 1 | ["cell19","cell6","cell5"] | [21,4,3] | [3] |

+---------+---------+-----------------------------+---------------+-------------------+--+

select gridid,height,collect_set(concat_ws(',',cell,cast(mrcount as string), cast(weakmrcount as string))) ascellArrayfrom(select gridid,height,cell,mrcount,weakmrcount,row_number()over(partition by gridid,height order by mrcount desc) rnfromtommyduan_testgroup bygridid,height,cell,mrcount,weakmrcount

) t10where rn<4

group bygridid,height+---------+---------+--------------------------------------------+--+

| gridid | height | cellarray |

+---------+---------+--------------------------------------------+--+

| g1 | 1 | ["cell3,23,3","cell2,22,3","cell19,21,3"] |

| g2 | 1 | ["cell19,21,3","cell6,4,3","cell5,3,3"] |

+---------+---------+--------------------------------------------+--+

selectgridid,height,

(case when size(cellArray)>0 then split(cellArray[0],'_')[0] else '-9999' end) ascell1,

(case when size(cellArray)>0 then split(cellArray[0],'_')[1] else '-9999' end) ascell1_mrcount,

(case when size(cellArray)>0 then split(cellArray[0],'_')[2] else '-9999' end) ascell1_weakmrcount,

(case when size(cellArray)>1 then split(cellArray[1],'_')[0] else '-9999' end) ascell2,

(case when size(cellArray)>1 then split(cellArray[1],'_')[1] else '-9999' end) ascell2_mrcount,

(case when size(cellArray)>1 then split(cellArray[1],'_')[2] else '-9999' end) ascell2_weakmrcount,

(case when size(cellArray)>2 then split(cellArray[2],'_')[0] else '-9999' end) ascell3,

(case when size(cellArray)>2 then split(cellArray[2],'_')[1] else '-9999' end) ascell3_mrcount,

(case when size(cellArray)>2 then split(cellArray[2],'_')[2] else '-9999' end) ascell3_weakmrcountfrom(select gridid,height,collect_set(concat_ws('_',cell,cast(mrcount as string), cast(weakmrcount as string))) ascellArrayfrom(select gridid,height,cell,mrcount,weakmrcount,row_number()over(partition by gridid,height order by mrcount desc) rnfromtommyduan_testgroup bygridid,height,cell,mrcount,weakmrcount

) t10where rn<4

group bygridid,height

) t12;+---------+---------+---------+----------------+--------------------+--------+----------------+--------------------+---------+----------------+--------------------+--+

| gridid | height | cell1 | cell1_mrcount | cell1_weakmrcount | cell2 | cell2_mrcount | cell2_weakmrcount | cell3 | cell3_mrcount | cell3_weakmrcount |

+---------+---------+---------+----------------+--------------------+--------+----------------+--------------------+---------+----------------+--------------------+--+

| g1 | 1 | cell3 | 23 | 3 | cell2 | 22 | 3 | cell19 | 21 | 3 |

| g2 | 1 | cell19 | 21 | 3 | cell6 | 4 | 3 | cell5 | 3 | 3 |

+---------+---------+---------+----------------+--------------------+--------+----------------+--------------------+---------+----------------+--------------------+--+