主页 / dba / 问题 / 167063
Accepted
userLP
Asked: 2017-03-14 11:33:50 +0800 CST

如何将这个 FULL OUTER JOIN 中的空结果与非空响应分组？

  • 772

我正在使用完全连接查看来自两个不同选择语句的用户 ID 的重叠和非重叠（唯一值）。主要区别在于一个表的 deal_id = 0，而另一个表的 deal_id 大于或等于 1。我加入了关于 exchange_id、pub_id 和 user_id 的 select 语句，但没有加入 deal_id。

字段增量值试图计算存在于池中的用户 deal_id >= 1 并且不存在于池中的 deal_id = 0（完全加入的主要原因）。

这是我输入的查询的简化，它在两个选择语句中：

SET
hive.auto.convert.join = TRUE
;

SELECT
    First.deal_id
    ,COALESCE( First.exchange_id, Second.exchange_id ) as exchange_id
    ,COALESCE( First.pub_id, Second.pub_id ) as pub_id
    ,COUNT (DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
    ,SUM (First.imps) AS First_imps
    ,SUM (Second.imps) AS Second_imps
    FROM
        (
            SELECT
                a.deal_id
                ,a.exchange_id
                ,a.pub_id
                ,a.user_id
                ,1 AS imps
            FROM
                logs a 
            WHERE
                a.deal_id >= 1
            AND a.event_type = 'TRUE'
        ) First 
        FULL JOIN (
            SELECT
                a.exchange_id
                ,a.pub_id
                ,a.user_id
                ,1 AS imps
            FROM
                logs a
            WHERE
            a.deal_id = 0
            AND a.event_type = 'TRUE'
        ) Second
        ON (
            First.exchange_id = Second.exchange_id
            AND First.pub_id = Second.pub_id
            AND First.user_id = Second.user_id
        )
        GROUP BY
        First.deal_id
        ,COALESCE( First.exchange_id, Second.exchange_id )
        ,COALESCE( First.pub_id, Second.pub_id )
;

这是我看到的结果：

DEAL_ID    EXCHANGE_ID    PUB_ID    INCREMENTAL    FIRST_IMPS    SECOND_IMPS
/N         4              1780      0              0             15
/N         4              1560      0              0             32
3389       4              1780      2              7             6
1534       4              1560      4              9             8

这是我想看到的：

DEAL_ID    EXCHANGE_ID    PUB_ID    INCREMENTAL    FIRST_IMPS    SECOND_IMPS
3389       4              1780      2              7             21
1534       4              1560      4              9             40

交易 ID 为空的结果与基于 exchange_id 和 pub_id 的非空交易 ID 的结果相匹配。

我能做些什么？

与此问题类似，但此解决方案不适用于此问题。

注意：我已经在 stackoverflow 上发布了这个问题，但我想我可以试试 dba

编辑：这是一个复制问题的 sqlfiddle，注意它使用的是 PostgreSQL，而我使用的是 hql

join coalesce

2 个回答

  1. 所有这些方法都是为了使您的原始查询成为派生表，然后按 pub_id 分组。

    SET hive.auto.convert.join = TRUE;

SELECT max(DEAL_ID) as DEAL_ID
     , EXCHANGE_ID
     , PUB_ID
     , sum(INCREMENTAL) as INCREMENTAL
     , sum(FIRST_IMPS) as FIRST_IMPS
     , sum(SECOND_IMPS) as SECOND_IMPS
  FROM (

    SELECT First.deal_id
         , COALESCE( First.exchange_id, Second.exchange_id ) as exchange_id
         , COALESCE( First.pub_id, Second.pub_id ) as pub_id
         , COUNT(DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
        , SUM(First.imps) AS First_imps
        , SUM(Second.imps) AS Second_imps
    FROM (SELECT a.deal_id
               , a.exchange_id
               , a.pub_id
               , a.user_id
               , 1 AS imps
            FROM logs a 
           WHERE a.deal_id >= 1
            AND a.event_type = 'TRUE'
         ) First 
FULL JOIN (SELECT a.exchange_id
                , a.pub_id
                , a.user_id
               , 1 AS imps
            FROM logs a
           WHERE a.deal_id = 0
             AND a.event_type = 'TRUE'
          ) Second
      ON (   First.exchange_id = Second.exchange_id
         AND First.pub_id = Second.pub_id
         AND First.user_id = Second.user_id
         )
   GROUP BY First.deal_id
          , COALESCE( First.exchange_id, Second.exchange_id )
          , COALESCE( First.pub_id, Second.pub_id )

  ) group by pub_id, exchange_id

    ;

    • 2
  2. Best Answer

    我发现这个解决方案有效。它不是很优雅，我担心规模过大（它是运行子查询两次还是一次），但它可以工作。这是小提琴

    WITH subquery as
       ( 
SELECT
    First.deal_id
    ,COALESCE( First.exchange, Second.exchange ) as exchange_id
    ,COALESCE( First.publisher, Second.publisher ) as pub_id
    ,COUNT (DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
    ,SUM (First.imps) AS First_imps
    ,SUM (Second.imps) AS Second_imps
    FROM
        (
            SELECT
                a.deal_id
                ,a.exchange
                ,a.publisher
                ,a.user_id
                ,1 AS imps
            FROM
                T1 a 
            WHERE
                a.deal_id >= 1
        ) First 
        FULL OUTER JOIN (
            SELECT
                a.exchange
                ,a.publisher
                ,a.user_id
                ,1 AS imps
            FROM
                T1 a
            WHERE
            a.deal_id = 0
        ) Second
        ON (
            First.exchange = Second.exchange
            AND First.publisher = Second.publisher
            AND First.user_id = Second.user_id
        )
        GROUP BY
        First.deal_id
        ,COALESCE( First.exchange, Second.exchange )
        ,COALESCE( First.publisher, Second.publisher )
        )

SELECT
deal.deal_id,
deal.exchange_id,
deal.pub_id,
sum(deal.incremental),
sum(deal.first_imps),
sum(coalesce(deal.second_imps, 0) + coalesce(oa.second_imps,0))
FROM 
subquery deal
LEFT JOIN 
subquery oa 
ON (deal.exchange_id = oa.exchange_id
AND deal.pub_id = oa.pub_id
AND oa.deal_id is null)
WHERE deal.deal_id is not null
GROUP BY
deal.deal_id,
deal.exchange_id,
deal.pub_id
;
    • 1

相关问题