查询优化- 10001

Question

我需要帮助来优化这个特定的查询。如果您看到有多个子查询正在运行，但它们在同一个表上运行。问题是GROUP BY被两个子查询使用，还有另外两个子查询没有使用GROUP BY。是否可以将这4个子查询组合在一起，只扫描一次表。

WITH f AS
(
  SELECT a.custom_referal_page,
     a.campaign_id,
     a.domain_user_id,
     a.event_name,
     COUNT(a.event_name),
     (SELECT COUNT(a1.event_name)
      FROM action_fact_new_wodim a1
      WHERE a1.domain_url = 'alternativeapparel.com'
      AND   a1.event_name = 'AltOrig PerfCapSl GoToPro'
     -- AND   a1.date_stamp BETWEEN '20140501' AND '20140530'          
     AND   a1.time_stamp BETWEEN '2014-05-01 14:43:15' AND '2014-05-30 14:43:15'
      AND   event_name != 'Page load'
      AND   event_name != 'Page unload') AS totalCount,
     (SELECT COUNT(domain_user_id)
      FROM (SELECT DISTINCT a1.domain_user_id,
                   a1.custom_referal_page
            FROM action_fact_new_wodim a1
            WHERE a1.domain_url = 'alternativeapparel.com'
            AND   a1.event_name = 'AltOrig PerfCapSl GoToPro'
      --            AND   a1.date_stamp BETWEEN '20140501' AND '20140530'          
           AND   a1.time_stamp BETWEEN '2014-05-01 14:43:15' AND '2014-05-30 14:43:15'
            AND   event_name != 'Page load'
            AND   event_name != 'Page unload') AS a2) AS uniqueCount,
     (SELECT COUNT(domain_user_id)
      FROM (SELECT DISTINCT domain_user_id
            FROM action_fact_new_wodim a1
            WHERE a1.domain_url = 'alternativeapparel.com'
            AND   a1.event_name = 'AltOrig PerfCapSl GoToPro'
       --     AND   a1.date_stamp BETWEEN '20140501' AND '20140530'          
            AND   a1.time_stamp BETWEEN '2014-05-01 14:43:15' AND '2014-05-30 14:43:15'
            AND   event_name != 'Page load'
            AND   event_name != 'Page unload') AS a2) AS totalUniqueCount
FROM action_fact_new_wodim a
WHERE a.domain_url = 'alternativeapparel.com'
AND   a.event_name = 'AltOrig PerfCapSl GoToPro'
-- AND   a.date_stamp BETWEEN '20140501' AND '20140530'          
AND   a.time_stamp BETWEEN '2014-05-01 14:43:15' AND '2014-05-30 14:43:15'
AND   event_name != 'Page load'
AND   event_name != 'Page unload'
GROUP BY a.custom_referal_page,
       a.campaign_id,
       a.domain_user_id,
       a.event_name
)

SELECT custom_referal_page,
    campaign_id,
    SUM(COUNT) AS COUNT,
    MAX(totalCount) AS totalCount,
    COUNT(uniqueCount) AS uniqueCount,
    MAX(totalUniqueCount) AS totalUniqueCount
FROM f
GROUP BY custom_referal_page,
    campaign_id
ORDER BY 3 DESC

输出: custom_referal_page campaign_id计数https://www.google.ca/ null 10838 20153 5346 9906 https://www.google.com/ null 3040 20153 1727 9906

Answer 1

您的查询非常疯狂，也许通过后退一步，这个选项可能对您有效。你所有的情况都是一样的，你只是得到了不同的计数。您应该能够通过group by表获得不同的计数和总计数，例如...

select
      a1.custom_referal_page,
      a1.campaign_id,
      COUNT(*) as TotalEvents,
      COUNT( distinct a1.event_name ) as CntDistEvents,
      COUNT( distinct a1.domain_user_id + a1.custom_referal_page ) as CntDistDomRef,
      COUNT( distinct a1.domain_user_id ) as CntDistUsers
   FROM 
      action_fact_new_wodim a1
   WHERE 
          a1.domain_url = 'alternativeapparel.com'
      AND a1.event_name = 'AltOrig PerfCapSl GoToPro'
      AND a1.time_stamp BETWEEN '2014-05-01 14:43:15' AND '2014-05-30 14:43:15'
   group by
      a1.custom_referal_page,
      a1.campaign_id

也就是说，我将在您的表( domain_url，event_name，time_Stamp )上建立一个索引，但是如果您实际上要在date_stamp上查询，请调整索引和where子句。

看看这为不同的引用提供的数字，并基于任何有意义的添加order by…