SQL is a great god to help me have a look at this problem, appreciate -

Title: this is a table called Order table "loans" Order: B as application time, C as application limits, D as the Order status, request now, every day in the user for the "purchase number" and "after purchase number
"
Required field output format: application date, the number of purchase, after purchase number

Figure is a table
Figure 2 is my solution (I feel it's terribly wrong)

CodePudding user response:

 
WITH CTE 
AS 
(SELECT *, 
ROW_NUMBER () OVER (PARTITION BY USER_ID ORDER BY ORDER_CREATETIME) AS an RN 
FROM the ORDER) 


The SELECT ORDER_CREATETIME, 
The SUM (a CASE WHEN an RN=1 THEN 1 ELSE 0 END) AS FIRST_ORDER, 
The SUM (CASE WHEN RN> 1 THEN 1 ELSE 0 END) AS REPEATED_ORDER 
The FROM CTE 
GROUP BY ORDER_CREATETIME 

CodePudding user response:

reference 1st floor RINK_1 response:

 
WITH CTE 
AS 
(SELECT *, 
ROW_NUMBER () OVER (PARTITION BY USER_ID ORDER BY ORDER_CREATETIME) AS an RN 
FROM the ORDER) 


The SELECT ORDER_CREATETIME, 
The SUM (a CASE WHEN an RN=1 THEN 1 ELSE 0 END) AS FIRST_ORDER, 
The SUM (CASE WHEN RN> 1 THEN 1 ELSE 0 END) AS REPEATED_ORDER 
The FROM CTE 
GROUP BY ORDER_CREATETIME 

thank god to baidu while I look at your answer, haven't learned a window function