I have the following table which is not loading: payment date, User ID, MonthlyAmount_last30d
User ID and payment date come from table. In the monthlyAmount_last30d field I want to see the amount for the period (payment date - 30 days).
I can't figure out how to group these columns correctly to get a result like this. If I only group by user_id, then I only see a first row with the payment date (I need all rows, each row has the payment total for that user id, the total is the payment for that row date - sum of 30 days).
If I group by both fields -> the payment amount I get is always equal to the payment amount for that row and not the actual amount for the period I need.
select
order_id,
order_date,
sum( if( ( order_date <= order_date
and order_date >= order_date - interval 30 day), order_cost_rub, 0)) monthlyAmount_last30d,
用户身份，
order_cost_rub
from MyTable
group by
user_id
Thanks for any tips!
renew:
Data example:
| user_id | Payment Date | order_cost_rub |
|---|---|---|
| 1 | 2022-01-01 12:00:00 | 100 |
| 1 | 2022-01-01 13:00:00 | 100 |
| 1 | 2022-01-03 20:00:00 | 150 |
| 2 | 2022-01-03 16:00:00 | 200 |
| 2 | 2022-01-15 11:00:00 | 300 |
Expected results:
| user_id | Payment Date | order_cost_rub | monthlyAmount_last30d |
|---|---|---|---|
| 1 | 2022-01-01 12:00:00 | 100 | 100 |
| 1 | 2022-01-01 13:00:00 | 100 | 200 |
| 1 | 2022-01-03 20:00:00 | 150 | 350 |
| 2 | 2022-01-03 16:00:00 | 200 | 200 |
| 2 | 2022-01-15 11:00:00 | 300 | 500 |
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
I think what you are trying in IF() is wrong. I think what you are asking for should be applied in a WHERE clause like
I want all transaction records for everyone that occurred within the past 30 days. From that, I want the total of all their purchases, grouped by each person. Sounds right?
select user_id, payment_date, order_cost_rub, sum( order_cost_rub ) OVER ( PARTITION BY user_id order by payment_date ) AS monthlyAmount_last30d from MyTable where order_date >= subdate(curdate(), interval 30 day)By applying the WHERE clause of the query, you never get any previous transaction records, so there is no need to apply IF() conditional checks on each record.
Revised query. So, you want to show every record, but only looking back 30 days. So, the WHERE clause still applies. But now I am showing all columns for all normal records. Take a look at SUM(). This is doing the cumulative total you requested.
Note the OVER clause after it (I indented it just to show its context relative to the sum() procedure). OVER specifies partitioning (similar to group by, but not an ordinary external group by). This simply means restarting when this partition value changes. In this case, it's the user ID. And the data is sorted in payment_date order through the ORDER BY instruction within the partition.
I think this should meet your needs.