0

I have below sample data, and I'm trying to get groups of record with below conditions:

  • Same prev_read_at
  • Different current_read_at
  • Only one record must have payment_id
  • Number of records in each group must be greater than one
  • The record with payment_id must have the least amount

Here is my sample data:

id location_id payment_id amount prev_read_at current_read_at
1 10 null 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
3 20 null 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
5 20 null 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.

4
  • Hi, and welcome to dba.se! I'm having difficulty in understanding your goal(s). Please go to dbfiddle.uk and choose the MariaDB 10.3 instance there - put in your table DDL and data DML. Kindly provide us with your desired output - so that we have input -> output and explain to us the logic that you used to arrive at this output. Images are frowned upon here for the reasons outlined in the link! Dec 21, 2021 at 14:03
  • @Vérace-getVACCINATEDNOW Thank you for your response. I've added a dbfiddle in the question and about images, I haven't added any images in the question. Dec 22, 2021 at 6:43
  • What is the problem you are having? You appear to have written a query which returns the results that you want? I would advise against grouping of ids and current_reads as comma-delimited strings - at least if they are for anything but the presentation layer? Is it a speed issue? Dec 22, 2021 at 7:18
  • As you can see in the results, the row that has 3,4,5 ids do not meet the last condition that I wrote in question. I want to omit this row in the result. Dec 22, 2021 at 7:25

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.