SQL Server Retrieve min and max store number that have the same location

kkbh8khc  于 2023-06-04  发布在  其他
关注(0)|答案(2)|浏览(163)

Our "store" table looks something like this:
| store_id | city_id | store_type |
| ------------ | ------------ | ------------ |
| 1 | 1 | regular |
| 2 | 1 | regular |
| 3 | 1 | regular |
| 50 | 1 | regular |
| 51 | 1 | express |
| 55 | 1 | express |
| 58 | 1 | express |
| 70 | 1 | express |
| 71 | 2 | regular |
| 75 | 2 | regular |
| 78 | 2 | regular |
| 80 | 2 | regular |
| 85 | 2 | regular |
| 90 | 2 | regular |
| 91 | 1 | regular |
| 95 | 1 | regular |
| 97 | 1 | regular |
| 100 | 1 | regular |
| 105 | 1 | regular |

I want to create a list in SQL Server that have the same value on 2 columns so in our table store we can make a select that looks something like:

min_store_idmax_store_idcity_idstore_type
1501regular
51701express
71902regular
911051regular

However the problem is, we hade a bad store_id system so the fact that the last and first row do have the same value is something that will happen in the table, and we sadly cannot change it.

Tried with something like this:

SELECT MIN(store_id) OVER (PARTITION BY city_id, store_type) AS min_store_id,
       MAX(store_id) OVER (PARTITION BY city_id, store_type) AS max_store_id,
       city_id,
       store_type
FROM store;

but it does not work at all.

rt4zxlrg

rt4zxlrg1#

You have a gaps and islands problem here, you could use the difference between two row_numbers approach to create groups :

with cte as (
  select *, row_number() over (order by store_id) 
          - row_number() over (partition by city_id, store_type order by store_id) as grp
  from store
)
SELECT MIN(store_id) AS min_store_id,
       MAX(store_id) AS max_store_id,
       max(city_id) as city_id,
       max(store_type) as store_type
FROM cte
group by grp

Result :

min_store_id    max_store_id    city_id store_type
1               50              1       regular
51              70              1       express
71              90              2       regular
91              105             1       regular

Demo here

6rqinv9w

6rqinv9w2#

The other answer is much cleaner, but since I put in the time I'm posting anyway.

As already stated its a gaps-and-islands problem, you need a way to mark each group you want to analyse.

Explanation in the SQL.

create table Test (store_id int, city_id int, store_type varchar(12));

insert into Test (store_id, city_id, store_type)
values
(1,     1,  'regular'),
(2,     1,  'regular'),
(3,     1,  'regular'),
(50,    1,  'regular'),
(51,    1,  'express'),
(55,    1,  'express'),
(58,    1,  'express'),
(70,    1,  'express'),
(71,    2,  'regular'),
(75,    2,  'regular'),
(78,    2,  'regular'),
(80,    2,  'regular'),
(85,    2,  'regular'),
(90,    2,  'regular'),
(91,    1,  'regular'),
(95,    1,  'regular'),
(97,    1,  'regular'),
(100,   1,  'regular'),
(105,   1,  'regular');

with cte1 as (
select *
  -- If the store_type or the city_id changes its a new group[
  , case when store_type <> lag(store_type, 1, store_type) over (order by store_id)
  or city_id <> lag(city_id, 1, city_id) over (order by store_id) then 1 else 0 end Transition
from Test
), cte2 as (
  select *
    -- Sum the transitions to provide a unique group id
    , sum(Transition) over (order by store_id) [Grouping]
  from cte1
)
select
  -- Calculate desired results per group
  min(store_id) min_store_id
  , max(store_id) max_store_id
  , city_id
  , store_type
from cte2
group by
  city_id
  , store_type
  , [Grouping]
order by min_store_id;

Returns

min_store_idmax_store_idcity_id store_type
1501
51701
71902
911051

Note: Providing the DDL+DML as shown here makes it much easier for people to answer.

db<>fiddle

相关问题