如何在SQL视图中基于关联行推导卡车配置schema列值

要给每个轮胎位置行添加上所属卡车的配置schema，你可以通过窗口函数+字符串聚合的方式实现，具体步骤如下：

核心思路 #

1. 先为每个轮胎行计算其所在车轴的轮胎总数
2. 按卡车（站点+卡车编号）分组，将各车轴的轮胎数按轴号排序后用x拼接成schema格式

实现SQL #

SELECT
    SITE,
    TRUCK,
    AXLE,
    POSNUM,
    -- 拼接得到卡车配置schema
    LISTAGG(axle_tire_count, 'x') WITHIN GROUP (ORDER BY AXLE) OVER (PARTITION BY SITE, TRUCK) AS truck_schema
FROM (
    SELECT
        SITE,
        TRUCK,
        AXLE,
        POSNUM,
        -- 计算当前轮胎所在车轴的总轮胎数
        COUNT(*) OVER (PARTITION BY SITE, TRUCK, AXLE) AS axle_tire_count
    FROM truck_tire_config -- 替换成你的视图名称
)

逻辑说明 #

• 内层子查询：用COUNT(*) OVER (PARTITION BY SITE, TRUCK, AXLE)窗口函数，为每一行标记出它所属车轴的轮胎总数，同一车轴的所有行都会得到相同的数值
• 外层查询：用LISTAGG(...) WITHIN GROUP (ORDER BY AXLE) OVER (PARTITION BY SITE, TRUCK)，在每个卡车的分组内，按轴号顺序将各轴的轮胎数用x连接，生成最终的配置schema（比如2x4）

验证示例 #

如果要查询CALGARY站点HAUL1卡车POSNUM=3的行，直接在上述SQL基础上添加过滤条件即可：

SELECT
    SITE,
    TRUCK,
    AXLE,
    POSNUM,
    truck_schema
FROM (
    SELECT
        SITE,
        TRUCK,
        AXLE,
        POSNUM,
        COUNT(*) OVER (PARTITION BY SITE, TRUCK, AXLE) AS axle_tire_count,
        LISTAGG(COUNT(*) OVER (PARTITION BY SITE, TRUCK, AXLE), 'x') WITHIN GROUP (ORDER BY AXLE) OVER (PARTITION BY SITE, TRUCK) AS truck_schema
    FROM truck_tire_config
)
WHERE SITE = 'CALGARY' AND TRUCK = 'HAUL1' AND POSNUM = 3;

这样就能得到该行对应的truck_schema为2x4，完全符合需求。

内容的提问来源于stack exchange，提问作者JMD