如何汇总SSMS 2017中的数据 [英] How to aggregate data in SSMS 2017
问题描述
创建表格gm
(
generated_id bigint,
[capmaing_id.x] bigint,
[campaign_id.y] bigint,
花费小数(18,2),
[date] datetime,
[utc_time.y] varchar(255),
realpurchase_cash decimal(18,2)
);
GO
SET DATEFORMAT DMY
INSERT INTO gm VALUES
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','07.10.2018-15:55',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5, '04 .10.2018','30.09.2018-12:00',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','05.10.2018-22 :34',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','01.10.2018-22:26',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,' 04.10.2018','06.10.2018-13:43',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','06.10.2018-13: 43',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','30.09.2018-12:23',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','30.09.2018-18:12',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5 ,'04 .10.2018','30.09.2018-11:22',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','05.10.2018- 22:35',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04 .10.2018','30.09.2018-11:23',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26 .09.2018','07.10.2018-15:55',1.49),
(1003477323030100,23843069854050700,23843069854050700 ,29.74,'26 .09.2018' ,"30.09 .2018-12:00',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26 .09.2018','05.10.2018-22:34',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26 .09.2018','01.10.2018-22:26',1.49),
(1003477323030100, 23843069854050700,23843069854050700,29.74,'26 .09.2018','06.10.2018-13:43',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26 .09.2018', '06 .10.2018-13:43',1.49);
$
GO
$
capmaing_id.x和campaign_id.y它是id
但是日期列是时候了 [capmaing_id.x]和 [utc_time.y]是时候[campaign_id.y]
1.每个capmaing_id(x和y)按时间顺序排序?
2.然后每个campaign_id计算从第一个日期到最后一个日期的总时间,并为每个ID计算总计最多60天。
for capmaing_id.x sum计算由 花柱(日期栏) ,但对于campaign_id.y总计最多60天由realpurchase_cash列计算utc_time.y列
如何创建此聚合结构
输出
Create table gm
(
generated_id bigint,
[capmaing_id.x] bigint,
[campaign_id.y] bigint,
spent decimal(18,2),
[date] datetime,
[utc_time.y] varchar(255),
realpurchase_cash decimal(18,2)
);
GO
SET DATEFORMAT DMY
INSERT INTO gm VALUES
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','07.10.2018-15:55',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','30.09.2018-12:00',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','05.10.2018-22:34',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','01.10.2018-22:26',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','06.10.2018-13:43',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','06.10.2018-13:43',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','30.09.2018-12:23',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','30.09.2018-18:12',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','30.09.2018-11:22',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','05.10.2018-22:35',1.49),
(1003477323030100,23843069854050700,23843069854050700,73.5,'04.10.2018','30.09.2018-11:23',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26.09.2018','07.10.2018-15:55',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26.09.2018','30.09.2018-12:00',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26.09.2018','05.10.2018-22:34',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26.09.2018','01.10.2018-22:26',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26.09.2018','06.10.2018-13:43',1.49),
(1003477323030100,23843069854050700,23843069854050700,29.74,'26.09.2018','06.10.2018-13:43',1.49);
GO
capmaing_id.x and campaign_id.y it is id
but date column it is time for [capmaing_id.x] and [utc_time.y] it is time for [campaign_id.y]
1.how each capmaing_id (x and y) order by time?
2.then for each campaign_id calculate total time from first to last date and for each id calculate sum up to 60 days.
for capmaing_id.x sum calculate by spent column (date column) , but for campaign_id.y sum up to 60 days calculates by realpurchase_cash column by utc_time.y column
How create this aggregating construction
output
其中 date.spend
这是常见的具体的campaign_id花费变量的天数。
date..realpurchase_cash
具体campaign_id的花费变量是常用时间。
Spend_AG
由花费值具体campaign_id汇总。 RP_AG
由realpurchase_cash值汇总具体campaign_id。
where date.spend
it is common time by days for spend variable of concrete campaign_id.
date..realpurchase_cash
it is common time by days for spend variable of concrete campaign_id.
Spend_AG
is aggregated by spend value concrete campaign_id. RP_AG
aggregated by realpurchase_cash value concrete campaign_id.
如何创建此类汇总结构
推荐答案
对不起您的要求不清楚上述说明
Sorry your requirement is not clear from above explanation
您希望如何执行聚合?
为什么utc_time的格式如上?它是实际的日期时间值还是更多的偏移量?
Why is utc_time having a format like above? is it actual datetime value or more of an offset?
这篇关于如何汇总SSMS 2017中的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!