时间戳

首页 > 解决方案 > 如何加入三个子查询并正确计算交叉点？

sql - 如何加入三个子查询并正确计算交叉点？

问题描述

我想知道我有多少忠实用户。其定义是用户需要每个滚动月至少登录一次。

如果用户在 2018-08-21 和 2018-09-21 之间登录一次，在 2018-09-21 和 2018-10-21 之间登录一次，在 2018-10-21 和 2018-11-21 之间登录一次，那么这是一个忠实的用户。

我认为实现这一目标的最佳方法是加入这三个时间定义的子查询。但我不知道如何计算它？每个子查询显示不同的计数。我是否必须将最低值视为交叉点？还是我做错了？

select p.country, round(SUM(a.GBytes+b.GBytes+c.GBytes), 2) as `Traffic`, a.Count, b.Count , c.Count
from (
    SELECT  username, 
            SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP, 
            (SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes, 
            count(username) as Count
    FROM radacct
    WHERE (`acctstarttime` BETWEEN '2018-08-21 22:13:54.286223' AND '2018-09-21 22:13:54.286223')
    GROUP BY username
) a join (
    SELECT  username, 
            SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP, 
            (SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes, 
            count(username) as Count
    FROM radacct
    WHERE (`acctstarttime` BETWEEN '2018-09-21 22:13:54.286223' AND '2018-10-21 22:13:54.286223')
    GROUP BY username
) b on a.username = b.username
join (
    SELECT  username, 
            SUBSTRING_INDEX( callingstationid, '=', 1 ) as IP, 
            (SUM(`acctinputoctets`)+SUM(`acctoutputoctets`))/1000/1000/1000 as GBytes, 
            count(username) as Count
    FROM radacct
    WHERE (`acctstarttime` BETWEEN '2018-10-21 22:13:54.286223' AND '2018-11-21 22:13:54.286223')
    GROUP BY username
) c on b.username = c.username
join cache_db.global_ip p on p.ip = a.IP
join cache_db.global_ip p2 on p2.ip = b.IP
join cache_db.global_ip p3 on p3.ip = c.IP
group by country;

结果： 在此处输入图像描述

标签： sqlmariadb

解决方案

由于您只关心用户是否在每个时间段内至少登录过一次，因此无需对每个用户在每个时间段执行计数。在给定时间段内连接的任何用户都将出现在表中a，b或者c分别出现在字段中，并且由于您将JOIN他们一起放在username字段中，因此只有在所有三个时间段内都登录过的用户才会出现在结果集中。因此COUNT(*)，您的外部查询中的简单（代替a.Count, b.Count , c.Count）将为您提供所需的结果。

如果username可以在不同的国家/地区使用相同的方法，那么您将需要在每个子查询中按username和分组，country并加入username和country。

推荐阅读