Requirements:
Statistics each user in occupancy within 7 days of total amount and total amount of work done,

1, in time, every day there are users in
2, revenue, belongs to the cumulative index quantity, contains the number of the day

Date of time_ru (in time) time_gz (work) userid (user ID) revenue (total income amount) quantity (total number)
20191001 20191001 aaa 1 1
20191001 20191002 aaa 4 3
September 8, 20191001, 20191003 aaa
12 12, 20191001, 20191004 aaa
20191001 20191005 aaa 18 17
20191001 20191006 aaa 25 20
20191001 20191007 aaa 30 22
20191024 20191024 BBB 5 2
20191024 20191025 BBB 6 8
20191024 20191026 BBB 12 10
18 14, 20191024, 20191027 BBB
20191024 20191028 BBB 22 19
28 22, 20191024, 20191029 BBB
20191024 20191030 BBB 35 26


I like writing, but always prompt error "ODPS - 0130071: [4 (] Semantic analysis exception - the function or the view 'time_ru' always be resolved
"

The select userid
, and count (distinct revenue)
, and count (distinct quantity)
, and count (distinct dateadd (time_ru (time_ru, 'yyyymmdd'), + 7, 'dd'))
From a table
Where ds>='20191001' and ds
='20191030'Group by userid

Consult everybody, how about to crazy date filter function to be play with

CodePudding user response:

Get everyone in the seventh day of the data can be

CodePudding user response:

reference 1/f, February 16 response:

get everyone in the seventh day data can it

right, so has been screening again, get "time_ru + 7", is right in theory, but still won't do

CodePudding user response:

revenue, belongs to the cumulative index quantity, contains the number of the day
You can forget the cumulative, directly read line.

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
Create table # T (time_ru Date, [time_gz] Date, [userid] nvarchar (23), int [revenue], [quantity] int) 
Insert # T 
Select '20191001', '20191001', N 'aaa', 1, 1 union all 
Select '20191001', '20191002', N 'aaa', 4, 3 union all 
Select '20191001', '20191003', N 'aaa' 9, 8 union all 
Select '20191001', '20191004', N 'aaa', 12, 12 union all 
Select '20191001', '20191005', N 'aaa' 18 union all 12 
Select '20191001', '20191006', N 'aaa', 25, 20 union all 
Select '20191001', '20191007', N 'aaa', 30, 22 union all 
Select '20191024', '20191024', N 'BBB', 5, 2 union all 
Select '20191024', '20191025', N 'BBB', 6, 8 union all 
Select '20191024', '20191026', N 'BBB' 12, 10 union all 
Select '20191024', '20191027', N 'BBB', 18, 14 union all 
Select '20191024', '20191028', N 'BBB', 22, 12 union all 
Select '20191024', '20191029', N 'BBB', 28, 22 union all 
Select '20191024', '20191030', N 'BBB', 35, 26 
Go 
- the end of the test data 
Select * from # T WHERE time_gz=DATEADD (DAY 6, time_ru) 

CodePudding user response:

refer to February 16 reply: 3/f

revenue, belongs to the cumulative index quantity, contains the number of the day
You can forget the cumulative, directly read line.

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
Create table # T (time_ru Date, [time_gz] Date, [userid] nvarchar (23), int [revenue], [quantity] int) 
Insert # T 
Select '20191001', '20191001', N 'aaa', 1, 1 union all 
Select '20191001', '20191002', N 'aaa', 4, 3 union all 
Select '20191001', '20191003', N 'aaa' 9, 8 union all 
Select '20191001', '20191004', N 'aaa', 12, 12 union all 
Select '20191001', '20191005', N 'aaa' 18 union all 12 
Select '20191001', '20191006', N 'aaa', 25, 20 union all 
Select '20191001', '20191007', N 'aaa', 30, 22 union all 
Select '20191024', '20191024', N 'BBB', 5, 2 union all 
Select '20191024', '20191025', N 'BBB', 6, 8 union all 
Select '20191024', '20191026', N 'BBB' 12, 10 union all 
Select '20191024', '20191027', N 'BBB', 18, 14 union all 
Select '20191024', '20191028', N 'BBB', 22, 12 union all 
Select '20191024', '20191029', N 'BBB', 28, 22 union all 
Select '20191024', '20191030', N 'BBB', 35, 26 
Go 
- the end of the test data 
Select * from # T WHERE time_gz=DATEADD (DAY 6, time_ru) 

-- -- -- -- -- --
Thank you, if the format is 2019/10/15 time_ru 18:28:01 like this, whether to add a definition format,

Select * from # T WHERE time_gz=DATEADD ((time_ru, 'yyyy/mm/dd hh: mm: ss'), 6, time_ru)

CodePudding user response:

To give a format conversion to date

 Select * from # T WHERE time_gz=DATEADD (DAY 6, CONVERT (DATE, time_ru))