工作小技巧--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, cday
order 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, cday
order by carno, cyear, cmonth, cda

4、命令解释： listagg(chour, ',') within group(order by chour) ， 将一个分组内的 chour 列的值排序后（order by chour）通过逗号连接起来。

注意，该函数是Oracle数据库内置函数，只有针对Oracle数据库的SQL语句才可以使用！