google-bigquery - BigQuery 不允许在 select 子句中使用 Subselect

Question

我有 2 个表的示例:

table POINT_STATUS:
rank_point  status
9       excellent
5       good
3       bad
0       fail

table STUDENT_POINT:
student     point
student A   8
student B   9
student C   5
student D   4

如何在 BigQuery 上创建查询以获取学生 (分数 >= rank_point) 的状态？

注意:-BigQuery 不允许在 select 子句中使用 Subselect。

Answer 1

NOTE: BigQuery not allow Subselect in select clause.

它确实支持标准 SQL - 参见 Enabling Standard SQL

WITH POINT_STATUS AS (
  SELECT 9 AS rank_point, 'excellent' AS status UNION ALL
  SELECT 5 AS rank_point, 'good' AS status UNION ALL
  SELECT 3 AS rank_point, 'bad' AS status UNION ALL
  SELECT 0 AS rank_point, 'fail' AS status
),
STUDENT_POINT AS (
  SELECT 'student A' AS student, 8 AS point UNION ALL
  SELECT 'student B' AS student, 9 AS point UNION ALL
  SELECT 'student C' AS student, 5 AS point UNION ALL
  SELECT 'student D' AS student, 4 AS point
)
SELECT 
  student, point,
  (SELECT status 
    FROM POINT_STATUS 
    WHERE rank_point <= point 
    ORDER BY rank_point DESC
    LIMIT 1
  ) AS status
FROM STUDENT_POINT

Meantime, if you are bound to Legacy SQL, see below

SELECT student, point, status FROM (
  SELECT 
    student, point, status, 
    ROW_NUMBER() OVER(PARTITION BY student ORDER BY rank_point DESC) AS pos
  FROM STUDENT_POINT
  CROSS JOIN POINT_STATUS
  WHERE point - rank_point >= 0
)
WHERE pos = 1