sqlite—SQLIn()运算符是否可以更有效地堆叠？

inkz8wg9 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(246)

我有以下代码，可以为以下访问者汇总前两个会话的页面浏览量：
已经下了订单，而且
已记录 session_index=1 ，和
已记录 session_index=2 在采样数据集中。

SELECT SUM(a.page_views)
FROM sessions a
WHERE a.id IN (
    SELECT b.id 
    FROM sessions b 
    WHERE b.order_id NOTNULL
        /*lookup for visitors who have made a purchase*/
)
AND a.id IN (
    SELECT c.id 
    FROM sessions c 
    WHERE c.session_index = 1
        /*lookup for visitors who have logged session_index #1*/
)
AND a.id IN (
    SELECT d.id
    FROM sessions d
    WHERE d.session_index = 2
        /*lookup for visitors who have logged session_index #2*/
)
AND a.session_index < 3;
    /*makes the SELECT SUM() add records with index #1 and #2.

它的效率相当糟糕，因为它分别执行三次查找比较。有没有更有效的方法来创建一个将查找表的三个条件合并为一个的查找表？

sql sqlite

来源：https://stackoverflow.com/questions/63241504/can-sql-in-operators-be-stacked-in-a-more-efficient-way

2条答案

按热度按时间

8qgya5xd1#

通过此查询可以获得满足条件的所有ID：

SELECT id
FROM sessions
GROUP BY id
HAVING COUNT(order_id) AND SUM(session_index = 1) AND SUM(session_index = 2)

你可以和接线员一起用 IN 要汇总页面浏览量：

SELECT SUM(page_views)
FROM sessions
WHERE session_index < 3
AND id IN (
    SELECT id
    FROM sessions
    GROUP BY id
    HAVING COUNT(order_id) AND SUM(session_index = 1) AND SUM(session_index = 2)
)

或者你可以用window函数 SUM() :

SELECT SUM(SUM(CASE WHEN session_index IN (1, 2) THEN page_views END)) OVER ()
FROM sessions
GROUP BY id
HAVING COUNT(order_id) AND SUM(session_index = 1) AND SUM(session_index = 2)

赞(0）回复(0）举报 2021-07-24

nxowjjhe2#

我建议采用两个聚合级别：

SELECT SUM(page_views)
FROM (SELECT s.id, SUM(s.page_views) as page_views
      FROM sessions s
      WHERE s.session_index < 3
      GROUP BY s.id
      HAVING CCOUNT(s.order_id) > 0 AND  -- users have made a purchase
             SUM(CASE WHEN s.session_index = 1 THEN 1 ELSE 0 END) > 0 AND
             SUM(CASE WHEN s.session_index = 2 THEN 1 ELSE 0 END) > 0
    ) s;

也就是说，您的原始版本具有正确的索引并使用 EXISTS 可能是最快的方法：

SELECT SUM(s.page_views)
FROM sessions s
WHERE a.session_index < 3 AND
      EXISTS (SELECT 1
              FROM sessions s2 
              WHERE s2.id = s.id AND s2.order_id NOT NULL
             ) AND
      EXISTS (SELECT 1
              FROM sessions s2 
              WHERE s2.id = s.id AND s2.session_index = 1
             ) AND
      EXISTS (SELECT 1
              FROM sessions s2 
              WHERE s2.id = s.id AND s2.session_index = 2
             ) ;

你想要的索引已经打开了 sessions(id, session_index, order_id) .

赞(0）回复(0）举报 2021-07-24

我来回答

sqlite—SQLIn()运算符是否可以更有效地堆叠？

2条答案

相关问题

热门标签

最新问答