试图查找在工作日、周末和两个weekday+weekend上登录的完全不同的客户端的总数。

Question

我的桌子结构如下：

node_id | client_id | timestamp
--------+-----------+-----------
    1   |    102    | 2012-02-01 (weekday)
--------+-----------+-----------
    2   |    104    | 2012-02-01 (weekday)
--------+-----------+-----------
    2   |    106    | 2012-02-02 (weekday)
--------+-----------+-----------
    1   |    106    | 2012-02-02 (weekend)
--------+-----------+-----------

(added fake weekday/weekend to simplify things)

我需要找到登录的client_id的总数：

• 工作日
• 周末
• 一个工作日和一个周末

在MSSQL中可以这样做吗？或者，我将不得不简单地将所有数据转储并解析到我的程序中吗？

编辑：

从上表中，所需的输出将告诉我：

3 people were logged on Mon-Fri by nodes 1 & 2
1 person was logged on Sat-Sun by nodes 1
1 person was logged on Mon-Sun by nodes 1 & 2

基本上，我需要知道有多少客户登录了星期一到星期五，卫星-太阳，星期一-太阳和哪个节点。

Answer 1

您可以使用日期部分(工作日，?)要找到一个日期的一周一天(见http://msdn.microsoft.com/en-us/library/ms174420.aspx)，那么这只是一个问题，指定谁是你感兴趣的分组。

Answer 2

如果我正确理解你，你可以试试这个

SET DATEFIRST 1
declare @tbl table (node_id int identity(1,1), client_id int, dtm datetime)

insert into @tbl (client_id,dtm) values (1,'20111001'), (1,'20111001'),(1,'20111002'),(1,'20111003'),(1,'20111004')
,(2,'20111001'), (2,'20111003'),(2,'20111003'),(2,'20111003'),(2,'20111004')
--weekday
select client_id, COUNT(*)
FROM @tbl
WHERE DATEPART(DW,dtm)<6
GROUP BY client_id
--weekend
select client_id, COUNT(*)
FROM @tbl
WHERE DATEPART(DW,dtm)>5
GROUP BY client_id

该解决方案使用达特帕特函数。要设置一周的第一天，请使用设置DATEFIRST。

在Server 2005+中编辑的您可以这样做：

SET DATEFIRST 1
DECLARE @tbl table (node_id int, client_id int, dtm datetime)

INSERT INTO @tbl (node_id,client_id,dtm) VALUES (1,102,'20120201'),(2,104,'20120201'),(2,106,'20120202'),(1,106,'20120204')

--weekday
SELECT CAST(COUNT(*) as varchar)+' people were logged on Mon-Fri by nodes '+
    (select cast(node_id as varchar)+',' as 'data()' from @tbl WHERE DATEPART(DW,dtm)<6 GROUP BY node_id for xml path(''))
FROM @tbl
WHERE DATEPART(DW,dtm)<6

--weekend
SELECT CAST(COUNT(*) as varchar)+' people were logged on Sat-Sun by nodes '+
    (select cast(node_id as varchar)+',' as 'data()' from @tbl WHERE DATEPART(DW,dtm)>5 GROUP BY node_id for xml path(''))
FROM @tbl
WHERE DATEPART(DW,dtm)>5

--all week
SELECT CAST(COUNT(*) as varchar)+' people were logged on Mon-Sun by nodes '+
    (select cast(node_id as varchar)+',' as 'data()' from @tbl GROUP BY node_id for xml path(''))
FROM @tbl

Answer 3

WITH myCTE AS (
SELECT node_id, clientid, datepart(weekday, timestamp) wkday
FROM YourTable
)
SELECT T1.node_id, T1.count(distinct clientid) total, 'weekdays' category
FROM myCTE T1
WHERE wkday in (2,3,4,5,6) --weekdays
UNION ALL
SELECT T1.node_id, T1.count(distinct clientid) total, 'weekends' category
FROM myCTE T1
WHERE wkday in (1,7) --weekends
SELECT T1.node_id, T1.count(distinct clientid) total, 'mon-sat' category
FROM myCTE T1
WHERE wkday in (2,3,4,5,6,7) --mon-sat

希望这能帮上忙。使用上面的脚本，您可以遵循这样的原则:如果需要更多不同天数组合的分组，只需继续添加一个union并更改where子句条件即可。