There is a table and column names are as follows:
Date of the product name product price
20180101 aaaa
18.0020180101 BBBB
5.0020180101 CCCCC 21.36
.
20180102 aaaa
10.1720180102 BBBB
17.3020180102 CCCCC 9.40
.





Product prices listed requirements: continuous 30, average more than 30 yuan product list,

CodePudding user response:

To product price is more than 30, name and date line weight
Row_number patition by product name order by date
And get the serial number is equal to 30, calculate the current date and the number of days on the first day is poor, poor to 29

CodePudding user response:

Try the following, never test

 
WITH CTE_1 
AS 
(SELECT *, DATEDIFF (DAY, '19700101', date) AS SEQ_1 
ROW_NUMBER () OVER (PARTITION BY product name ORDER BY date) AS SEQ_2 
FROM the TABLE) 

SELECT a product name, SEQ_1 - SEQ_2 
GROUP BY product name, SEQ_1 - SEQ_2 
HAVING a COUNT (1)=30 AND AVG (prices) & gt; 30 

CodePudding user response:

 with t as (
- to get the price more than 30 products, and in accordance with the date and name row heavy 
Select distinct date, name of the product from TB where prices & gt; 30 
), t1 as (
- according to the name of the group, according to the date generated number 
Select *, row_number () over (partition by product name order by date) as sn from t 
) 
- select a serial number is greater than the current number of 29, and it is more than 29 days date products 
- serial number 29 said more than 30 product record 
- date and serial number difference is equal, said is continuous date 
Select distinct product name 
The from t1 a 
Where the exists (
Select the top 1 1 
The from t1 
Where the product name=a. product name 
29 and sn=a.s n + 
And datediff (d, a. date, date)=29 
) 

CodePudding user response:

1. Continuous 30, this condition is not very clear, can't give you said
2. This condition, despite the continuous 30, give you a command, refer to the

Select a product name, min (prices)
From table
Where the date & gt; 20180101 the and date & lt; 20180131
Group by product name
Having min (prices) & gt; 30

CodePudding user response:

There are ways to your your requirement,

1. The row_number
2. Using a temp table