如何通过此查询获取最多10个行和10分钟的行? [英] How to get the 10 max and 10 min of rows with this query?

问题描述

我正在使用以下查询来查找与最近2天相比我的页面pageviews中的差异

I am using the following query to find the difference in pageviews of my pages comparing the last 2 days

SELECT PAGE, COUNT( * ) AS poso,
       (SUM(DATE(TIMESTAMP) = CURDATE()) - 
        SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 
DAY))
       ) diff
FROM  `behaviour` 
WHERE DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 
DAY ) 
GROUP BY PAGE;

查询返回的页面为原样.但是，我要做的是只让正差最大的10行和负差最大的10行.

The query returns me as pages as there are. However, what I want to do is to only have the 10 rows with the biggest positive difference, and the 10 withe th biggest negative difference.

我该如何实现?

推荐答案

使用两个查询的 union ，一个查询获得 Top 10 ，另一个查询获得底部10 .查询如下所示:

Use a union of two queries, one gets the Top 10 and the other gets the bottom 10. The query would look like this:

更新后的答案

SELECT 
    PAGE
    , poso
    , diff
from
    (
        (
            SELECT PAGE, COUNT( * ) AS poso,
                (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                    SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                ) diff
            FROM  `behaviour` 
            WHERE 
                DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) 
            GROUP BY PAGE
            ORDER BY (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                        SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                    ) desc
            LIMIT 10
        ) 
        UNION
        (   
            SELECT PAGE, COUNT( * ) AS poso,
                (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                    SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                ) diff
            FROM  `behaviour` 
            WHERE 
                DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) 
            GROUP BY PAGE
            ORDER BY (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                        SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                    ) asc
            LIMIT 10
        ) 
    ) as u
order by diff;

这篇关于如何通过此查询获取最多10个行和10分钟的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！