CASE WHEN 判断中以嵌套子查询为条件的 Hive SQL 查询

Question

正如标题，我的查询如下：

SELECT
COUNT(
    DISTINCT (CASE WHEN id IN (
        SELECT DISTINCT id
        FROM some_table
        WHERE year = 2019
        AND month IN (1, 2, 3)
    ) tmp AND (*OTHERS CONDITIONS*) THEN id END)
) AS devices
FROM some_table
WHERE year = 2019
AND month IN (1, 2, 3);

但是，我不断收到此错误：

cannot recognize input near 'SELECT' 'DISTINCT' 'id' in expression specification

我该如何解决？

Answer 1

你不能像这样使用子查询。将子查询更改为 join 并在以防万一时使用它。

SELECT
COUNT(
    DISTINCT (CASE WHEN tmpid is not null 
         AND (*OTHERS CONDITIONS*) THEN id END)
) AS devices
FROM some_table
left join (SELECT DISTINCT id as tmpid
        FROM some_table
        WHERE year = 2019
        AND month IN (1, 2, 3)
    ) tmp on tmp.tmpid = id
WHERE year = 2019
AND month IN (1, 2, 3);