BigQuery选择数据集报错及按referrer列排序问题求助

报错原因分析 #

你的SQL存在多处语法和逻辑错误，具体如下：

• CTE NewQ1语法顺序错误：ORDER BY子句位置违规，SQL正确执行顺序应为SELECT ... FROM ... [GROUP BY ...] ORDER BY ...，你将ORDER BY COUNT(chat_start_url)放在SELECT与FROM之间，违反语法规则。
• 聚合函数未搭配分组：COUNT(chat_start_url)是聚合函数，使用时必须配合GROUP BY指定分组字段（此处应按referrer分组），否则会触发BigQuery语法报错。
• 外层SELECT语法冗余：最后一行SELECT NewQ1.*,末尾多了一个逗号，属于无效语法。
• Chat_Kernel的GROUP BY逻辑冗余：当前Chat_Kernel中无聚合操作，仅用GROUP BY实现去重，改用DISTINCT会更清晰高效。

按referrer排序的正确实现方案 #

根据需求，分两种常见场景提供正确SQL：

场景1：按referrer分组统计链接数量并按数量排序 #

如果目标是统计每个referrer对应的聊天链接数量，再按数量排序：

WITH Chat_Kernel AS (
  SELECT DISTINCT
    conferenceId, 
    chat_creation_date_Asia_Kolkata, 
    chat_start_date_Asia_Kolkata, 
    chat_start_url, 
    referrer, 
    chat_duration_in_seconds, 
    queue_duration_in_seconds, 
    visitor_livechat_id, 
    visitor_nick, 
    visitor_ip, 
    visitor_email, 
    last_operator_id, 
    `group`, 
    group_name, 
    rate, 
    last_rate_comment, 
    goal_action_name
  FROM `bigquery-405008.Kernel_GA4.Kernel_chat` 
  LIMIT 1000
),
NewQ1 AS (
  SELECT
    referrer,
    COUNT(chat_start_url) AS chat_url_count
  FROM Chat_Kernel
  GROUP BY referrer
  ORDER BY chat_url_count DESC -- DESC为降序，改为ASC则为升序
)
SELECT * FROM NewQ1

场景2：直接按referrer字段的字母顺序排序（无分组统计） #

如果仅需将所有记录按referrer字段本身排序：

WITH Chat_Kernel AS (
  SELECT DISTINCT
    conferenceId, 
    chat_creation_date_Asia_Kolkata, 
    chat_start_date_Asia_Kolkata, 
    chat_start_url, 
    referrer, 
    chat_duration_in_seconds, 
    queue_duration_in_seconds, 
    visitor_livechat_id, 
    visitor_nick, 
    visitor_ip, 
    visitor_email, 
    last_operator_id, 
    `group`, 
    group_name, 
    rate, 
    last_rate_comment, 
    goal_action_name
  FROM `bigquery-405008.Kernel_GA4.Kernel_chat` 
  LIMIT 1000
)
SELECT * FROM Chat_Kernel
ORDER BY referrer ASC -- ASC为升序，改为DESC则为降序

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