文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（联盟区域）/ DWS性能调优/ SQL调优案例/ 案例：典型不下推语句整改案例集/ 案例："Row () Expression" can not be shipped
更新时间：2025-12-12 GMT+08:00
查看PDF
分享

案例："Row () Expression" can not be shipped

问题根因

语句中有row表达式，一般表现为在targetlist（输出列SELECT 和 FROM之间）上存在括号表达式"(......)"，

处理方案：改写SQL，消除row表达式。

案例1：row表达式(k4.businessID, arriveDate)出现在targetlist中

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
SELECT
    date_format(arriveDate, '%Y-%m-%d') AS "date",
    sales,
    COUNT(DISTINCT (k4.businessID, arriveDate)/*row expression*/)
FROM (
    SELECT k1.businessID, k2.arriveDate, k1.sales
    FROM potential_customers k1
    LEFT JOIN flow_reception k2
        ON k1.businessID = k2.businessID
    WHERE k2.arriveDate BETWEEN '2024-08-31' AND '2024-09-02'
) k4
GROUP BY arriveDate, sales;

规避方案：不直接输出row表达式，因此改写row表达式为字符串拼接。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
 
SELECT
    date_format(arriveDate, '%Y-%m-%d') AS "date",
    sales,
    COUNT(DISTINCT k4.businessID ||'-' ||  arriveDate/*Eliminate row expressions*/)
FROM (
    SELECT k1.businessID, k2.arriveDate, k1.sales
    FROM potential_customers k1
    LEFT JOIN flow_reception k2
        ON k1.businessID = k2.businessID
    WHERE k2.arriveDate BETWEEN '2024-08-31' AND '2024-09-02'
) k4
GROUP BY arriveDate, sales;

修改点对比