0

## SQL 取第一筆加總問題

SELECT sum(???) FROM
(
SELECT * FROM db GROUP BY BeginTimePeriodDt
) AS g

> 只取同一時間區間的第一筆

kenkotw iT邦新手 5 級 ‧ 2018-10-05 17:24:49 檢舉

kenkotw iT邦新手 5 級 ‧ 2018-10-05 17:27:19 檢舉

### 2 個回答

0

iT邦大師 1 級 ‧ 2018-10-05 17:10:43

DB 名稱是 AgentActivitySummary

BeginTimePeriodDt User_Id Service_Id LoginDt WorkGroup_Id EndTimePeriodDt Site_Id TenantId TotalLoginTime TotalIdleTime

select a.user_id
, sum(case when (a.service_id ='0') then a.TotalLoginTime else null end) as TotalLoginTime
, sum(case when (a.service_id ='0') then a.TotalIdleTime else null end) as TotalidleTime
, sum(case whenservice_id not in ('0') and a.service_id not in ('53000462')) then a.TotalLoginTime else null end) as ServiceLoginTime
, sum( case when (a.service_id not in ('0') and a.service_id not in ('53000462')) then a.TotalIdleTime else null end) as ServiceidleTime

from AgentActivitySummary a
where (a.BeginTimePeriodDt>='2018-10-03' and a.BeginTimePeriodDt <='2018-10-04')
--and a.user_id ='RY16013' --and (service_id not in ('0', '53000462'))
group by a.user_id

CTE概念，可以把它當成變數保存查詢就可以

``````with CTE as (
select * from AgentActivitySummary
where [Service_Id] not in ('53000485','53000577') /*先排除53000485, 53000577，後面篩選玩第一筆資料，使用union all把資料加回來*/
)
,CTE2 as (
select row_number() over (
partition by [BeginTimePeriodDt], [User_Id] --BeginlimePeriodDt同一時間區間分組
order by [Service_Id] desc /*Service倒序，以便後面取最大第一筆*/
) rnk,* from AgentActivitySummary
where [Service_Id] in ('53000485','53000577')  /*service_ID (53000485, 53000577) 我只要取其中一筆就好 ， (可以取 Service_id 最大的那筆)*/
) T
where rnk = 1
),CTE3 as (
select * from CTE
union all
select * from CTE2
)
select a.user_id
, sum(case when (a.service_id ='0') then a.TotalLoginTime else null end) as TotalLoginTime
, sum(case when (a.service_id ='0') then a.TotalIdleTime else null end) as TotalidleTime
, sum(case when (service_id not in ('0','53000462')) then a.TotalLoginTime else null end) as ServiceLoginTime
, sum(case when (a.service_id not in ('0','53000462')) then a.TotalIdleTime else null end) as ServiceidleTime
from CTE3 a
where (a.BeginTimePeriodDt>='2018-10-02' and a.BeginTimePeriodDt <='2018-10-04')
group by a.user_id

``````

### 測試資料

``````CREATE TABLE AgentActivitySummary
([BeginTimePeriodDt] datetime, [User_id] varchar(7), [Service_Id] int, [LoginDt] datetime, [WorkGroup_Id] int, [EndTimePeriodDt] varchar(23)
, [Site_Id] int, [TenantId] int, [TotalLoginTime] int, [TotalIdleTime] int)
;

INSERT INTO AgentActivitySummary
([BeginTimePeriodDt], [User_id], [Service_Id], [LoginDt], [WorkGroup_Id], [EndTimePeriodDt], [Site_Id], [TenantId], [TotalLoginTime], [TotalIdleTime])
VALUES
('2018-10-02 09:30:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:45:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-0210:00:00.000', 54, 1, 900, 0),
('2018-10-02 09:45:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-02 10:00:00.000', 54, NULL, 900, 0),
('2018-10-02 09:45:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-02 10:00:00.000', 54, NULL, 900, 0),
('2018-10-02 09:45:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-0210:00:00.000', 54, NULL, 900, 0),
('2018-10-02 10:00:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-02 10:15:00.000', 54, NULL, 900, 0),
('2018-10-02 10:00:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-0210:15:00.000', 54, NULL, 900, 0),
('2018-10-02 10:00:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-0210:15:00.000', 54, 1, 900, 0),
('2018-10-02 10:00:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-0210:15:00.000', 54, 1, 900, 0)
;
``````

## 線上測試連結:SQL Fiddle

kenkotw iT邦新手 5 級 ‧ 2018-10-05 18:16:06 檢舉

``````select sum(totallogintime+TotalldleTime) /*進行totallogintime & totalidletime加總*/
as totalSum
from (
select * from (
select row_number() over (
partition by BeginlimePeriodDt --BeginlimePeriodDt同一時間區間分組
order by Service desc /*Service倒序，以便後面取最大第一筆*/
) rnk,*
from TestTable
)T1 where rnk = 1 /*取Service最大第一筆*/
) T2
``````

## 測試連結 : SQL Fiddle

kenkotw iT邦新手 5 級 ‧ 2018-10-05 18:28:58 檢舉

Sorry,我是 SQL 門外漢

as totalSum
from (
select * from (
select row_number() over (
partition by BeginlimePeriodDt --BeginlimePeriodDt同一時間區間分組
order by Service_id desc /Service倒序，以便後面取最大第一筆/
) rnk,*
from AgentActivitySummary
)T1 where rnk = 1 /取Service最大第一筆/
) T2

Msg 207, Level 16, State 1, Line 6
Invalid column name 'BeginlimePeriodDt'.

kenkotw iT邦新手 5 級 ‧ 2018-10-06 12:30:20 檢舉

DB 名稱是 AgentActivitySummary

BeginTimePeriodDt User_Id Service_Id LoginDt WorkGroup_Id EndTimePeriodDt Site_Id TenantId TotalLoginTime TotalIdleTime

DB 欄位長相如下圖

select a.user_id
, sum(case when (a.service_id ='0') then a.TotalLoginTime else null end) as TotalLoginTime
, sum(case when (a.service_id ='0') then a.TotalIdleTime else null end) as TotalidleTime
, sum(case whenservice_id not in ('0') and a.service_id not in ('53000462')) then a.TotalLoginTime else null end) as ServiceLoginTime
, sum( case when (a.service_id not in ('0') and a.service_id not in ('53000462')) then a.TotalIdleTime else null end) as ServiceidleTime

from AgentActivitySummary a
where (a.BeginTimePeriodDt>='2018-10-03' and a.BeginTimePeriodDt <='2018-10-04')
--and a.user_id ='RY16013' --and (service_id not in ('0', '53000462'))
group by a.user_id

rogeryao iT邦大師 2 級 ‧ 2018-10-06 18:51:13 檢舉

to 暐翰

('2018-10-02 09:30:00', 'sl10048','53000485', 720, 200),
sl10048 不會出現,
partition by [BeginTimePeriodDt]

rogeryao iT邦大師 2 級 ‧ 2018-10-06 21:05:41 檢舉

to 暐翰:

http://sqlfiddle.com/#!18/722c0/1

http://sqlfiddle.com/#!18/6772c/1

rogeryao iT邦大師 2 級 ‧ 2018-10-06 22:57:17 檢舉

to 暐翰:

1.我用 max ; 你用 row_number() over (partition by ..order by ..desc <= 取得第一筆前要先做排序
2.我直接 sum ; 你用 sum(case when ....<= sum 之前都要判斷
3.我用 full outer join +兩次 group by , 你用 union + 一次 group by <= 造成我的 SQL 成本較高的原因 ?
4.若此table 有 1 萬筆或是 10 萬筆資料時,跑出來的成本(或時間)會是如何 ? 你有辦法模擬測試嗎 ? 謝謝.

rogeryao iT邦大師 2 級 ‧ 2018-10-07 13:13:57 檢舉

to 暐翰:

1.where [Service_Id] not in ('53000485','53000577')
=>where [Service_Id] in ('0','53000462')
2.where [Service_Id] in ('53000485','53000577')
=>where [Service_Id] not in ('0','53000462')
3.測試天數 10000 ,每天 10 人 ,每人連線 Service_Id = 40,總資料 400 萬筆,不考慮 BeginTimePeriodDt 篩選變數
4.我的 SQL 約 1 秒 ,你的 SQL 約 13 秒 [ row_number() over (partition by ..order by ..desc <= 成本 71% ]

rogeryao sorry IT邦怪怪的，你留言我都看不到訊息通知

1.我用 max ; 你用 row_number() over (partition by ..order by ..desc <= 取得第一筆前要先做排序

2.我直接 sum ; 你用 sum(case when ....<= sum 之前都要判斷

3.我用 full outer join +兩次 group by , 你用 union + 一次 group by <= 造成我的 SQL 成本較高的原因 ?

4.若此table 有 1 萬筆或是 10 萬筆資料時,跑出來的成本(或時間)會是如何 ? 你有辦法模擬測試嗎 ? 謝謝.

rogeryao iT邦大師 2 級 ‧ 2018-10-08 21:05:04 檢舉

to 暐翰

1
rogeryao
iT邦大師 2 級 ‧ 2018-10-06 12:08:19

2.原需求 :只取同一時間區間的第一筆進行加總
3.新需求 :但同一時間區段 2018-10-02 09:30:00.000 會有二筆 Service_ID,可以取 Service_id 最大的那筆 <== 已經與第一筆條件無關

([BeginTimePeriodDt] datetime, [Service_Id] int, [TotalLoginTime] int, [TotalIdleTime] int)
;

([BeginTimePeriodDt], [Service_Id], [TotalLoginTime], [TotalIdleTime])
VALUES
('2018-10-02 09:30:00', 53000485, 720, 200),
('2018-10-02 09:30:00', 53000577, 720, 200),
('2018-10-02 09:45:00', 53000485, 900, 300),
('2018-10-02 09:45:00', 53000577, 900, 400),
('2018-10-02 10:00:00', 53000485, 900, 0),
('2018-10-02 10:00:00', 53000577, 900, 0),
('2018-10-02 10:15:00', 53000485, 900, 0),
('2018-10-02 10:15:00', 53000577, 900, 0)
;

from LoginTime as B
inner join (
select A.BeginTimePeriodDt,
max(A.Service_Id) as Service_Id
from LoginTime as A
where 1=1
group by A.BeginTimePeriodDt ) as C on C.BeginTimePeriodDt=B.BeginTimePeriodDt
and C.Service_Id=B.Service_Id

http://sqlfiddle.com/#!18/bac51/4

rogeryao iT邦大師 2 級 ‧ 2018-10-06 14:36:16 檢舉

kenkotw iT邦新手 5 級 ‧ 2018-10-06 12:30:20 描述的需求又變了

([BeginTimePeriodDt] datetime, [User_Id] nvarchar(7), [Service_Id] nvarchar(8), [TotalLoginTime] int, [TotalIdleTime] int)
;
([BeginTimePeriodDt], [User_Id],[Service_Id], [TotalLoginTime], [TotalIdleTime])
VALUES
('2018-10-02 09:30:00', 'sl10047','0', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000462', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000485', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000485', 720, 200),
('2018-10-02 09:30:00', 'sl10047','0', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000462', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000485', 720, 200),
('2018-10-02 09:30:00', 'sl10047','53000577', 720, 200),
('2018-10-02 09:45:00', 'sl10047','0', 900, 300),
('2018-10-02 09:45:00', 'sl10047','53000462', 900, 300),
('2018-10-02 09:45:00', 'sl10047','53000485', 900, 300),
('2018-10-02 09:45:00', 'sl10047','53000577', 900, 400),
('2018-10-02 10:00:00', 'sl10047','0', 900, 0),
('2018-10-02 10:00:00', 'sl10047','53000462', 900, 0),
('2018-10-02 10:00:00', 'sl10047','53000485', 900, 0),
('2018-10-02 10:00:00', 'sl10047','53000577', 900, 0),
('2018-10-02 10:15:00', 'sl10047','0', 900, 0),
('2018-10-02 10:15:00', 'sl10047','53000462', 900, 0),
('2018-10-02 10:15:00', 'sl10047','53000485', 900, 0),
('2018-10-02 10:15:00', 'sl10047','53000577', 900, 0)
;

from (
select MA.User_Id,
sum(isnull(MA.TotalIdleTime,0)) as TotalIdleTime
from LoginTime as MA
where 1=1
and (MA.BeginTimePeriodDt>='2018-10-01' and MA.BeginTimePeriodDt <='2018-10-04')
and MA.Service_Id='0'
group by MA.User_Id
) as X
full outer join (
from LoginTime as B
inner join (
select A.User_Id,A.BeginTimePeriodDt,
max(A.Service_Id) as Service_Id
from LoginTime as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id <>'0'
and A.Service_Id <>'53000462'
group by A.User_Id,A.BeginTimePeriodDt) as C on C.User_Id=B.User_Id
and C.BeginTimePeriodDt=B.BeginTimePeriodDt
and C.Service_Id=B.Service_Id
where 1=1
group by B.User_Id
) as Y on Y.User_Id=X.User_Id
where 1=1
order by X.User_Id

rogeryao iT邦大師 2 級 ‧ 2018-10-06 18:37:04 檢舉

select X.User_Id,

select
case when X.User_Id is null then Y.User_Id
else X.User_Id end as User_Id,

rogeryao iT邦大師 2 級 ‧ 2018-10-06 18:46:05 檢舉

order by X.User_Id

order by User_Id

kenkotw iT邦新手 5 級 ‧ 2018-10-08 09:37:20 檢舉

kenkotw iT邦新手 5 級 ‧ 2018-10-08 15:16:10 檢舉

rogeryao 您好

('2018-10-02 09:30:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),

from (
select MA.User_Id,
sum(isnull(MA.TotalIdleTime,0)) as TotalIdleTime
--sum(case when (MA.service_id not in ('53000566','53000567')) then MA.TotalLoginTime else null end) as SACBLoginTime
--sum(case when (MA.service_id not in ('53000566','53000567')) then MA.TotalLoginTime else null end) as SACBidleTime
from AgentActivitySummary as MA
where 1=1
and (MA.BeginTimePeriodDt>='2018-10-03' and MA.BeginTimePeriodDt <='2018-10-04')
and MA.Service_Id='0'
group by MA.User_Id
) as X
full outer join (
from AgentActivitySummary as B
inner join (
select A.User_Id,A.BeginTimePeriodDt,
max(A.Service_Id) as Service_Id
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-03' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485') --不計算 MANUAL, SACB, AutoAgent
group by A.User_Id,A.BeginTimePeriodDt) as C on C.User_Id=B.User_Id
and C.BeginTimePeriodDt=B.BeginTimePeriodDt
and C.Service_Id=B.Service_Id
where 1=1
group by B.User_Id
) as Y on Y.User_Id=X.User_Id
where 1=1
order by X.User_Id

rogeryao iT邦大師 2 級 ‧ 2018-10-08 20:52:04 檢舉

CREATE TABLE AgentActivitySummary
([BeginTimePeriodDt] datetime, [User_id] varchar(7), [Service_Id] int, [LoginDt] datetime, [WorkGroup_Id] int, [EndTimePeriodDt] varchar(23)
, [Site_Id] int, [TenantId] int, [TotalLoginTime] int, [TotalIdleTime] int);

INSERT INTO AgentActivitySummary
([BeginTimePeriodDt], [User_id], [Service_Id], [LoginDt], [WorkGroup_Id], [EndTimePeriodDt], [Site_Id], [TenantId], [TotalLoginTime], [TotalIdleTime])
VALUES
('2018-10-02 09:30:00', 's110047', 0, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 90),
('2018-10-02 09:30:00', 's110047', 53000462, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 0),
('2018-10-02 09:30:00', 's110047', 53000485, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000486, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000487, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 900, 0),
('2018-10-02 09:30:00', 's110047', 53000577, '2018-10-02 09:32:59', 53000132, '2018-10-02 09:45:00.000', 54, 1, 720, 20)
;

select distinct V0.User_Id,
from AgentActivitySummary as V0
-- A.Service_Id='0' & TotalLoginTime
left join
(
select K1.User_Id,
from (
select
A.User_Id,A.BeginTimePeriodDt,
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id='0'
group by A.User_Id,A.BeginTimePeriodDt
) AS K1
where 1=1
Group by K1.User_Id
) as V1 on V1.User_Id=V0.User_Id
-- A.Service_Id='0' & TotalIdleTime
left join
(
select K2.User_Id,
sum(K2.TotalIdleTime) as TotalIdleTime
from (
select
A.User_Id,A.BeginTimePeriodDt,
max(TotalIdleTime) as TotalIdleTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id='0'
group by A.User_Id,A.BeginTimePeriodDt
) AS K2
where 1=1
Group by K2.User_Id
) as V2 on V2.User_Id=V0.User_Id
-- A.Service_Id= other & ServiceLoginTime
left join
(
select K3.User_Id,
from (
select
A.User_Id,A.BeginTimePeriodDt,
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485')
group by A.User_Id,A.BeginTimePeriodDt
) AS K3
where 1=1
Group by K3.User_Id
) V3 on V3.User_Id=V0.User_Id
-- A.Service_Id= other & ServiceIdleTime
left join
(
select K4.User_Id,
sum(K4.TotalIdleTime) as ServiceIdleTime
from (
select
A.User_Id,A.BeginTimePeriodDt,
max(TotalIdleTime) as TotalIdleTime
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485')
group by A.User_Id,A.BeginTimePeriodDt
) AS K4
where 1=1
Group by K4.User_Id
) V4 on V4.User_Id=V0.User_Id
order by V0.User_Id

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=678100406f83d6a85486202a21e94a45

rogeryao iT邦大師 2 級 ‧ 2018-10-08 22:01:56 檢舉

select distinct V0.User_Id,
from AgentActivitySummary as V0
-- A.Service_Id='0' & TotalLoginTime
left join
(
select K1.User_Id,
from (
select
A.User_Id,A.BeginTimePeriodDt,
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id='0'
group by A.User_Id,A.BeginTimePeriodDt
) AS K1
where 1=1
Group by K1.User_Id
) as V1 on V1.User_Id=V0.User_Id
-- A.Service_Id= other & ServiceLoginTime
left join
(
select K3.User_Id,
from (
select
A.User_Id,A.BeginTimePeriodDt,
from AgentActivitySummary as A
where 1=1
and (A.BeginTimePeriodDt>='2018-10-01' and A.BeginTimePeriodDt <='2018-10-04')
and A.Service_Id not in ('0','53000462','53000566','53000567','53000485')
group by A.User_Id,A.BeginTimePeriodDt
) AS K3
where 1=1
Group by K3.User_Id
) V3 on V3.User_Id=V0.User_Id
order by V0.User_Id

rogeryao iT邦大師 2 級 ‧ 2018-10-09 00:01:24 檢舉

SQL 語法無誤 , 測試資料 s110047 <= 字串有 key 錯

kenkotw iT邦新手 5 級 ‧ 2018-10-09 10:30:10 檢舉