工作小技巧--Oracle列转行函数 listagg 的使用

1、首先叙述一下业务需求，我们有一张车辆位置信息表，其中包含：车牌号、年、月、日、时、经度、维度 7列信息（车辆上的定位设备会定时上报），我们需要通过SQL语句获取一辆车在特定某一天（年月日）有多少个小时会上报经纬度信息（即上报信息的设备工作正常），以及都是哪些小时在上报（一个字符串表示即可，比如 20,21,22 表明这一天20点21点22点上报了经纬度信息）。建表语句如下:CREATE TABLE CARINFO ( CARNO VARCHAR2(32), CYEAR INTEGER, CMONTH INTEGER, CDAY INTEGER, CHOUR INTEGER, CLON NUMBER, CLAT NUMBER );最后需要达到的查询结果如图3示。

2、通过业务需求的描述，我们首先知道需要通过 group 水瑞侮瑜by 语句将数据进行汇总统计，至于统计一辆车具体喋笊脎脶某一天上报经纬度的小时数量可以通过 count 聚合函数进行实现，所以我们写了如下SQL语句：select carno, cyear, cmonth, cday, count(chour) from (select distinct carno, cyear, cmonth, cday, chour from carinf)group by carno, cyear, cmonth, cdayorder by carno, cyear, cmonth, cday注意：在这个业务表中因为一辆车在同一个小时内会上报多次数据，上报一次我们即认可定位设备工作正常，所以我们通过一个子查询删除同一小时内的重复数据。

3、通过上面的语句，我们已经完成了业务的一半需求，那另一半痒滕熘丬呢？我们就需要使用今天的主角了： listagg烫喇霰嘴() within group(order by) 函数 ！ 这是一个列转行函数，我们知道在使用 group by 分组操作后，如果想获取非分组列，只能通过聚合函数进行（比如 sum, max, min等），但这些聚合函数通常用于处理数值，对于基于字符类型的操作（比如我们本例的需求：将多个数值通过逗号连接起来），只能通过这个函数进行，SQL语句如下：select carno, cyear, cmonth, cday, count(chour), listagg(chour,',') within group(order by chour) from (select distinct carno, cyear, cmonth, cday, chour from carinfo)group by carno, cyear, cmonth, cdayorder by carno, cyear, cmonth, cda

4、命令解释： listagg(chour, ',') within group(order by chour) ， 将一个分组内的 chour 列的值排序后（order by chour）通过逗号连接起来。注意，该函数是Oracle数据库内置函数，只有针对Oracle数据库的SQL语句才可以使用！