SQL 每天获取注册用户的总数

Question

你能帮我构造一个查询吗？场景是这样的，我想每天获得注册用户的总价值。

Answer 1

Select  count(*) 
from    tableName
Where   registered = true 
Group by Date

Answer 2

假设您的注册时间戳存储在created_at日期时间字段和名为 say user 的表中：

SELECT created_at,COUNT(*) as `total registration` FROM `user` GROUP BY (DATE(`user`.`created_at`))

Answer 3

前一个答案既不涵盖where部分，也不输出日期名称。

这应该可以同时执行这两项操作并产生您想要的结果：

create table #data (
reg_id int,
reg_email nvarchar(255),
reg_date datetimeoffset(7)
)
insert into #data(reg_id, reg_email, reg_date) 
VALUES
(1, 'a', '2018-10-01'),
(2, 'b', '2018-10-01'),
(3, 'c', '2018-10-02'),
(4, 'd', '2018-10-03'),
(5, 'e', '2018-10-01'),
(6, 'f', '2018-10-02'),
(7, 'g', '2018-10-04'),
(8, 'h', '2018-10-05'),
(9, 'i', '2018-10-05'),
(10, 'j', '2018-10-06')
SELECT count(*), datename(dw, reg_date) from #data
where datepart(week, reg_date) = 40
group by reg_date
drop table #data

假设您使用的是大于或等于 2008sql server！

Answer 4

我终于得到了今天和过去 7 天的正确数据。请参考下面的查询。

SELECT 
a.JourneyName,
a.BonusName,
a.Status,
a."Timestamp",
a.MID,
COUNT(CASE WHEN DATEDIFF(DD, a."Timestamp", GETDATE()) = 0 THEN 1 ELSE NULL END) as "Day_1",
COUNT(CASE WHEN DATEDIFF(DD, a."Timestamp", GETDATE()) = 1 THEN 1 ELSE NULL END) as "Day_2",
COUNT(CASE WHEN DATEDIFF(DD, a."Timestamp", GETDATE()) = 2 THEN 1 ELSE NULL END) as "Day_3",
COUNT(CASE WHEN DATEDIFF(DD, a."Timestamp", GETDATE()) = 3 THEN 1 ELSE NULL END) as "Day_4",
COUNT(CASE WHEN DATEDIFF(DD, a."Timestamp", GETDATE()) = 4 THEN 1 ELSE NULL END) as "Day_5",
COUNT(CASE WHEN DATEDIFF(DD, a."Timestamp", GETDATE()) = 5 THEN 1 ELSE NULL END) as "Day_6",
COUNT(CASE WHEN DATEDIFF(DD, a."Timestamp", GETDATE()) = 6 THEN 1 ELSE NULL END) as "Day_7"
FROM
TableName a
WHERE
a."Timestamp" >= DATEADD(DD, -7, GETDATE())
AND a."Timestamp" <= GETDATE()
GROUP BY``
a.JourneyName, a.BonusName, a.Status, a."Timestamp", a.MID

每天的总计数，其中Day_1是今天，Day_2是昨天，依此类推。