mysql有可能吗? [英] Is this possible with mysql?

问题描述

首先:对不起标题，但是也许以后我会找到更好的标题.

First of all: sorry for the title, but maybe I will find a better one later.

几分钟前我问了这个问题，但是由于我无法描述我想要的内容，因此我会再次尝试:)

I asked this some minutes ago, but since I was not able to describe what I want I try it again :)

这是我的表结构: http://sqlfiddle.com/#!2/b25f9/37

该表用于存储用户会话.

The table is used to store user sessions.

除此之外，我想生成一个堆积的条形图，该条形图应显示我有多少活跃用户.我的想法是，我会像这样根据过去几天的在线时间对用户进行分组

Out of this I would like to generate a stacked bar chart that should show how many active users I have. My idea was that I group the users based on their online-times of the last days like this

让我们说一下星期五:

• B组:星期四(今天)在线的用户
• C组:周四(星期三)(今天)不是在线的用户
• D组:周四或周三不在在线，但周二(和今天)在线的用户
• E组:不在周四，周三或周二但上周一，周日或周六(今天)在线的用户

• 组A:与其他组不匹配(但仅在今天)的用户

• Group B: Users that were online thursday (and today)
• Group C: Users that were not online thursday but wednesday (and today)
• Group D: Users that were not online thursday or wednesday but tuesday (and today)
• Group E: Users that were not online thursday, wednesday or tuesday but last monday, sunday or saturday (and today)

• Group A: Users that do not match the other groups (but were only today)

我只想知道这些组(特定日期)的用户数

I only want to know the number of users in those groups (for a specific day)

推荐答案

另一个更新:偶然地(通过复制和粘贴)有starttime = ... or starttime = ...，但应为starttime = ... or endtime = ...

Another Update: Accidently (by copy&paste) had starttime = ... or starttime = ... but it should be starttime = ... or endtime = ...

更新:

为更详细地解释我的查询(在最终查询中还有更多评论):

To explain my query in more detail (in the final query there are even more comments):

首先，我们得到了

SELECT
...
FROM gc_sessions s
WHERE DATE(starttime) = CURDATE() OR DATE(endtime) = CURDATE()

这无非是说给我所有今天开始会话或今天结束会话的用户".不得不一次又一次地考虑这两次，使得查询有点笨拙，但实际上并没有那么复杂.

That's nothing more like saying "give me all users whose session started today or ended today". Having to consider those two times again and again makes the query a bit clumsy, but actually it's not that complicated.

因此，显然，通常我们将使用COUNT()函数对某些事物进行计数，但是，由于我们要进行条件计数"，因此我们仅使用SUM()函数并告诉它何时加1，何时不加1.

So, usually we would use the COUNT() function to count something, obviously, but since we want "conditional counting", we simply use the SUM() function and tell it when to add 1 and when not.

SUM (CASE WHEN ... THEN 1 ELSE 0 END) AS a_column_name

SUM()函数现在检查从今天开始的会话结果集中的每一行.因此，对于该结果集中的每个用户，我们将查看该用户是否在指定的日期在线.不管他/她在线多少次，所以出于性能原因，我们使用EXISTS.使用EXISTS，您可以指定一个子查询，该子查询将在找到某项后立即停止，因此，只要不是NULL，只要找到某项，返回的内容都没有关系.因此，请不要困惑为什么选择1.在子查询中，我们必须将外部查询中当前正在检查的用户与内部查询(子查询)中的用户连接起来，并指定时间窗口.如果所有条件均满足计数1，则返回0，如前所述.

The SUM() function examines now each row in the result set of sessions from today. So for each user in this result set we look if this user was online the date we specify. It doesn't matter how many times he/she was online, so for performance reasons we use EXISTS. With EXISTS you can specify a subquery which stops as soon as something is found, so it doesn't matter what it returns when something is found, as long as it's not NULL. So don't get confused why I selected 1. In the subquery we have to connect the user which is currently examined from the outer query with the user from the inner query (subquery) and specify the time window. If all criterias meet count 1 else 0 like explained before.

SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndYesterday,

然后我们为每个条件和一列创建一列，您只需在一个查询中即可满足所有条件.因此，随着您更新的问题，您的条件已更改，我们只需要添加更多规则:

Then we make a column for each condition and voila, you have all you need in one query. So with your updated question your criteria has changed, we just have to add more rules:

SELECT
/*this is like before*/
SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS FridayAndThursday,
SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 2 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 2 DAY)))
         /*this one here is a new addition, since you don't want to count the users that were online yesterday*/
         AND NOT EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) = CURDATE() - INTERVAL 1 DAY)))
    THEN 1 ELSE 0 END) AS FridayAndWednesdayButNotThursday,
SUM(CASE WHEN 
         EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) = CURDATE() - INTERVAL 3 DAY) /* minus 3 days to get tuesday*/
                       OR (date(endtime) = CURDATE() - INTERVAL 3 DAY)))
         /*this is the same as before, we check again that the user was not online between today and tuesday, but this time we really use BETWEEN for convenience*/
         AND NOT EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                  AND ((date(starttime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY) 
                       OR (date(endtime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY)))
    THEN 1 ELSE 0 END) AS FridayAndTuesdayButNotThursdayAndNotWednesday,
.../*and so on*/
FROM gc_sessions s
WHERE DATE(starttime) = CURDATE() OR DATE(endtime) = CURDATE()

所以，我希望您现在就明白了.还有其他问题吗?随便问.

So, I hope you get the idea now. Any more questions? Feel free to ask.

更新结束

上一个问题的答案:

select 
SUM(CASE WHEN EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                      AND ((date(starttime) = CURDATE() - INTERVAL 1 DAY) 
                           OR (date(starttime) = CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndYesterday,
SUM(CASE WHEN EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                      AND ((date(starttime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY) 
                           OR (date(starttime) BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndYesterdayOrTheDayBeforeYesterday,
SUM(CASE WHEN EXISTS (SELECT 1 FROM gc_sessions sub_s WHERE s.user = sub_s.user 
                      AND ((date(starttime) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY) 
                           OR (date(starttime) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() - INTERVAL 1 DAY))) 
    THEN 1 ELSE 0 END) AS todayAndWithinTheLastWeek
from gc_sessions s
where date(starttime) = CURDATE()
or date(endtime) = CURDATE()

这篇关于mysql有可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！