I have below sample data, and I’m trying to get groups of record with below conditions:
- Only one record must have
- Number of records in each group must be greater than one
- The record with
payment_idmust have the least
Here is my sample data:
||237003000||2021-07-03 01:00:00||2021-09-01 01:00:00|
|2||10||1741||139664000||2021-07-03 01:00:00||2021-11-16 01:00:00|
||0||2021-07-04 01:00:00||2021-08-02 01:00:00|
|4||20||1655||6820000||2021-07-04 01:00:00||2021-09-06 01:00:00|
||39000||2021-07-04 01:00:00||2021-09-08 01:00:00|
I’ve applied all conditions except the last one in below query.
select GROUP_CONCAT(id), location_id, GROUP_CONCAT(current_read_at) from bills where prev_read_at between '2021-07-01 12:01:01' and '2021-07-05 12:01:01' group by CONCAT(`location_id` ,`prev_read_at`) having count(id)>1 AND count(payment_id)=1 AND COUNT(DISTINCT current_read_at)>1
The desirable result must contain only records with
location_id = 10.
I think the last condition must be applied on each group, but don’t know how to implement this in having or are there any other method for this?
Server version: 10.3.18-MariaDB – MariaDB Server
Here is the dbfiddle of my question. As you can see, I want to filter records with ids of
3,4,5 in my result. Because
3,4,5 group don’t have payment_id with the least amount in its group.
You should consider using window functions for applying such complex conditions. After select your criteria over different partitions, you can limit your results over those criteria. Below query is derived by considering your conditions in this fiddle.
SELECT * FROM ( select *, COUNT(payment_id) OVER (PARTITION BY prev_read_at,location_id) payed_count, COUNT(location_id) OVER (PARTITION BY prev_read_at,location_id) loc_count, min(case when payment_id is null then amount end) OVER (PARTITION BY prev_read_at,location_id) min_amt, min(case when payment_id is not null then amount end) OVER (PARTITION BY prev_read_at,location_id) payed_min_amt, DENSE_RANK() OVER (PARTITION BY location_id, prev_read_at ORDER BY current_read_at ASC) cur_rank from bills where prev_read_at between '2021-07-01 12:01:01' and '2021-07-05 12:01:01' ) A WHERE payed_count=1 AND loc_count>payed_count AND payed_min_amt+1 < min_amt+1 AND cur_rank > 1 AND payment_id is not null;
Hope it works.