时间戳

首页 > 解决方案 > 无法在多个公用表表达式中绑定标识符

sql - 无法在多个公用表表达式中绑定标识符

问题描述

我正在使用“使用 SQL 和 Excel 进行数据分析”一书中的 SQLBook 数据库来显示总运输天数高于平均总运输天数的州的平均运输天数。我正在使用 2 个公用表表达式：

WITH orderDetails (days, state) 
AS(
    SELECT DATEDIFF(day, o.OrderDate, ol.ShipDate), o.State 
    FROM SQLBook.dbo.Orders o
    JOIN [SQLBook].dbo.OrderLines ol
    ON ol.OrderId = o.OrderId
)
,
/* This finds the overall average shipping days */
AvgShipping (avgShip)
AS(
    SELECT AVG(DATEDIFF(day, o.OrderDate, ol.ShipDate))
    FROM SQLBook.dbo.Orders o
    JOIN [SQLBook].dbo.OrderLines ol
    ON ol.OrderId = o.OrderId
)

SELECT
    state,
    AVG(days) AS "Average days to ship"
FROM orderDetails
GROUP BY state
HAVING AVG(days) > AvgShipping.avgShip
ORDER BY state

问题是我不断收到“无法绑定多部分标识符“AvgShipping.avgShip””错误。请让我知道这个查询的问题出在哪里。

谢谢！

标签： sqlcommon-table-expression

解决方案

您需要放入外部查询AvgShipping的from子句，以便您可以使用它的列：

WITH ...
SELECT od.state, AVG(od.days) AS average_days_to_ship
FROM orderDetails od
CROSS JOIN AvgShipping s
GROUP BY od.state, s.avgShip
HAVING AVG(od.days) > s.avgShip
ORDER BY od.state

但我认为使用窗口函数可以更有效地完成这项任务：

select *
from (
    select state, 
        avg(datediff(day, o.orderdate, ol.shipdate)) as avg_days_state,
        1.0 * sum(sum(datediff(day, o.orderdate, ol.shipdate))) over() 
            / sum(count(*)) over() as avg_days_overall
    from  sqlbook.dbo.orders o
    inner join sqlbook.dbo.orderlines ol on ol.OrderId = o.OrderId
    group by state
) t
where avg_days_state > avg_days_overall

我们可以通过避免聚合和使用来简化逻辑distinct：

select distinct state, avg_days_state, avg_days_overall
from (
    select state, 
        avg(datediff(day, o.orderdate, ol.shipdate)) over(partition by o.state) as avg_days_state,
        avg(datediff(day, o.orderdate, ol.shipdate)) over() as avg_days_overall
    from  sqlbook.dbo.orders o
    inner join sqlbook.dbo.orderlines ol on ol.OrderId = o.OrderId
) t
where avg_days_state > avg_days_overall

推荐阅读