sql - AWS Athena中的嵌套查询替代方案

Question

我正在运行一个查询，该查询给出了不重叠的first_party_id集-与一个第三方(而不是另一个第三方)相关联的ID。此查询不在雅典娜中运行，但是，给出错误:Correlated queries not yet supported.
正在查看prestodb文档https://prestodb.io/docs/current/sql/select.html(Athena是prestodb的幕后花絮)，作为嵌套查询的替代方法。对于此with statement子句，给出的not in示例似乎不太好翻译。想知道嵌套查询的替代方案是什么-下面的查询。

SELECT 
         COUNT(DISTINCT i.third_party_id) AS uniques
FROM 
         db.ids i
WHERE
         i.third_party_type = 'cookie_1'
         AND i.first_party_id NOT IN (
           SELECT
             i.first_party_id
           WHERE 
             i.third_party_id = 'cookie_2'
         )

Answer 1

也许有更好的方法可以做到-我也很好奇!我能想到的一种方法是使用外部联接。 (我不确定您的数据的结构如何，因此请原谅人为的示例，但我希望它能很好地解决问题。)

with 
  a as (select * 
       from (values 
       (1,'cookie_n',10,'cookie_2'), 
       (2,'cookie_n',11,'cookie_1'),
       (3,'cookie_m',12,'cookie_1'),
       (4,'cookie_m',12,'cookie_1'),
       (5,'cookie_q',13,'cookie_1'),
       (6,'cookie_n',13,'cookie_1'),
       (7,'cookie_m',14,'cookie_3')
       ) as db_ids(first_party_id, first_party_type, third_party_id, third_party_type)
      ),
  b as (select first_party_type 
        from a where third_party_type = 'cookie_2'),
  c as (select a.third_party_id, b.first_party_type as exclude_first_party_type 
        from a left join b on a.first_party_type = b.first_party_type 
        where a.third_party_type = 'cookie_1')
select count(distinct third_party_id) from c 
       where exclude_first_party_type is null;

希望这可以帮助!