-1

We have a situation to get unique count between saved range of mysql table. lets say, I have data saved in table like below:

sr_no start_sr_no end_sr_number total_count
1 1 10 10
2 5 15 11
3 27 35 9
4 11 21 11

now see in above records, row sr_no 2 is overlapping the row sr_no 1 and row sr_no 4 so the unique total_count of all 3 range should be: 21 instead of 10+11+11 = 32 and all range total count should be:30 instead of 10+11+9+11:41

Dharman
  • 30,962
  • 25
  • 85
  • 135
Rakesh Verma
  • 1

1 Answers1

1

10.4.17-MariaDB – Rakesh Verma

WITH
cte1 AS ( SELECT start_sr_no value, 1 weight FROM test
          UNION ALL
          SELECT end_sr_number, -1 FROM test ),
cte2 AS ( SELECT *,
                 SUM(weight) OVER (ORDER BY value, weight DESC) cost
          FROM cte1 ),
cte3 AS ( SELECT value, 
                 SUM(cost = 0) OVER (ORDER BY value DESC) grp
          FROM cte2 )
SELECT MIN(value) start_sr_no, 
       MAX(value) end_sr_number, 
       MAX(value) - MIN(value) + 1 total_count
FROM cte3
GROUP BY grp
ORDER BY 1

For total values only use

WITH
cte1 AS ( SELECT start_sr_no value, 1 weight FROM test
          UNION ALL
          SELECT end_sr_number, -1 FROM test ),
cte2 AS ( SELECT *,
                 SUM(weight) OVER (ORDER BY value, weight DESC) cost
          FROM cte1 ),
cte3 AS ( SELECT value, 
                 SUM(cost = 0) OVER (ORDER BY value DESC) grp
          FROM cte2 ),
cte4 AS ( SELECT MIN(value) start_sr_no, 
                 MAX(value) end_sr_number, 
                 MAX(value) - MIN(value) + 1 total_count
          FROM cte3
          GROUP BY grp )
SELECT SUM(total_count) total_count
FROM cte4

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=a627f3276bf2fd60d6bcfc986d4c19d9

needed same query for lower versions of mysql as well such as MySQL5.5 – Rakesh Verma

SELECT MIN(start) start_sr_no,
       MAX(finish) end_sr_number,
       MAX(finish) - MIN(start) + 1 total_count
FROM ( SELECT CASE WHEN start_sr_no > @prev_finish
            THEN @group := @group + 1
                   ELSE @group
                   END grp,
              CASE WHEN start_sr_no BETWEEN @prev_start AND @prev_finish
                   THEN @prev_start
                   ELSE @prev_start := start_sr_no
                   END start, 
              CASE WHEN end_sr_number < @prev_finish
                   THEN @prev_finish
                   ELSE @prev_finish := end_sr_number
                   END finish
       FROM test
       CROSS JOIN ( SELECT @prev_start := -1,
                           @prev_finish := -1,
                           @group := 0 ) init_variables
       ORDER BY start_sr_no, end_sr_number ) enumerated
GROUP BY grp

and

SELECT SUM(total_count) total_count
FROM ( SELECT MIN(start) start_sr_no,
              MAX(finish) end_sr_number,
              MAX(finish) - MIN(start) + 1 total_count
       FROM ( SELECT CASE WHEN start_sr_no > @prev_finish
                   THEN @group := @group + 1
                          ELSE @group
                          END grp,
                     CASE WHEN start_sr_no BETWEEN @prev_start AND @prev_finish
                          THEN @prev_start
                          ELSE @prev_start := start_sr_no
                          END start, 
                     CASE WHEN end_sr_number < @prev_finish
                          THEN @prev_finish
                   ELSE @prev_finish := end_sr_number
                   END finish
                   FROM test
              CROSS JOIN ( SELECT @prev_start := -1,
                                  @prev_finish := -1,
                                  @group := 0 ) init_variables
              ORDER BY start_sr_no, end_sr_number ) enumerated
       GROUP BY grp ) total

https://dbfiddle.uk/?rdbms=mysql_5.5&fiddle=e2e3e6a6bd00aca2f8c53de50638788d

Akina
  • 39,301
  • 5
  • 14
  • 25