有关 SQL 查询的帮助 [英] Help on an SQL query

问题描述

我有一个名为 activity 的表，有 2 列:

I have a table named activity with 2 columns:

when as datetime // last time i saw a user
guid as varchar  // a unique identifier for each user

此表用于跟踪特定资源的使用情况.Guid 由客户创建.

This tables is used to track usage in a certain resource. Guid are created by the clients.

我希望能够查询我在过去一天/一周/一个月内有多少新用户.新用户由首次出现在活动表中的 guid 标识.

I want to be able to query how many new users I have in the last day/week/month. A new user is identified by a guid appearing in the activity table for the first time.

如何在单个 SQL 语句中执行此操作?

How can I do this in a single SQL statement?

更新:该表包含用户每次使用资源时的条目.所以如果他用了5次，就会有5行.在用户使用资源之前，我没有他的 guid，表中也没有他的条目.

update: The table contains an entry for every time a user is using the resource. So if he used 5 times, there will be 5 rows. Until a user uses the resource, i don't have his guid and there is no entry in table for him.

结果:

谢谢大家，帮了大忙.对于任何感兴趣的人，这是我根据您的所有建议汇编的:

Thank you all, it helped a lot. For anyone interested, this is what I compiled from all your suggestions:

SET @duration=7;
SELECT COUNT(distinct guid) AS total_new_users FROM `activity`
    WHERE `when` >= DATE_SUB(CURDATE(),INTERVAL @duration DAY) AND guid NOT IN
    (
        SELECT guid
        FROM `activity`
        WHERE `when` < DATE_SUB(CURDATE(),INTERVAL @duration DAY)
    );

推荐答案

select count(guid)as total_new_users
from activity
where when >= {last day/week/month}
and guid not in(select guid
from activity
where when < {last day/week/month})

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