PostgreSQL/Redshift执行聚合查询时出现“could not identify an ordering operator for type record”错误求助

嘿，我来帮你解决这个问题！你遇到的这个错误其实是Redshift对SQL语法的解析细节导致的，很容易踩坑。

错误原因 #

你写的order by(i.spektr_date, i.campaign_id)会被Redshift解析成一个单一的record类型表达式，而不是你预期的“按spektr_date和campaign_id两个字段排序”。Redshift没有默认的排序运算符来处理record类型，所以就抛出了这个错误。

解决方案 #

只需要把ORDER BY后面的括号去掉就行，这是最简洁的写法：

order by i.spektr_date, i.campaign_id

如果非要保留括号的话，得给每个字段单独加括号（不过完全没必要，第一种写法更直观）：

order by (i.spektr_date), (i.campaign_id)

修改后的完整查询 #

我把你的查询修正后贴在这里，重点标注了修改的部分：

select
i.spektr_date,
i.campaign_id,
avg(i.advertiser_id) as Advertiser_Id,
max(i.ad_program_type) as ad_program_type,
max(CAST(start_date AS DATE)) as Start_Date,
max(CAST(end_date AS DATE)) as End_Date,
max(cam.campaign_name) as Campaign_Name ,
max(cam.status) as Campaign_Status,
max(cam.budget_currency_code) as Currency_Code,
max(cam.daily_budget) as Budget,
max(cam.smart_bidding_strategy) as Bidding_Strategy,
max(cam.campaign_strategy) as Campaign_Strategy,
sum(i.total_clicks) as Clicks,
sum(i.total_impressions) as Impressions,
sum(i.cpc*i.total_clicks) as Spend,
sum(co.sale_price * co.sale_quantity) as Sales,
sum(i.total_clicks)/sum(i.total_impressions) as CTR,
(
sum(co.sale_price * co.sale_quantity) / sum(i.cpc * i.total_clicks)
) as ROAS,
(
SUM(i.cpc * i.total_clicks) / SUM(co.sale_price * co.sale_quantity)
) as ACOS
from
spektr_ach.d_sp_ad_impressions i
inner join spektr_ach.d_sp_ad_conversions co on i.advertiser_id = co.advertiser_id
and i.campaign_id = co.campaign_id and i.spektr_date = co.spektr_date and i.ad_program_type = co.ad_program_type
and i.traffic_attributes = co.traffic_attributes and i.cpc = co.cpc and i.ad_group_id = co.ad_group_id
and i.ad_id = co.ad_id and i.session_id = co.session_id
and i.customer_id = co.customer_id and i.marketplace_id = co.marketplace_id
inner join spektr_vulcan.o_campaigns cam on cam.campaign_id = co.campaign_id
and cam.marketplace_id  = CAST(co.marketplace_id AS BIGINT) and cam.advertiser_id = co.advertiser_id
where i.advertiser_id = 40420525505 and
i.traffic_attributes = 1
and i.ad_program_type = 'SPONSORED_PRODUCTS'
and i.total_clicks > 0
and i.total_impressions > 0
and i.spektr_date = date'2022-09-17' and i.spektr_date < date'2022-09-25'
group by (i.spektr_date,
i.campaign_id)
order by i.spektr_date, i.campaign_id; -- 此处为修改后的排序子句

另外补充一句，你GROUP BY里的括号写法是没问题的，Redshift允许把分组字段用括号括起来，但ORDER BY的解析逻辑不一样，不能照搬这种写法，这也是容易混淆的地方。

备注：内容来源于stack exchange，提问作者Surya