динамическая группировка по в sql server

#sql #sql-server #stored-procedures

#sql #sql-сервер #хранимые процедуры

Вопрос:

я хочу создать хранимую процедуру, подобную makeGroupBy(@a int,@b int,@c int) . Входные данные равны 0 или 1, чтобы решить, по каким столбцам группировать. Моя попытка на данный момент приведена ниже:

 -- exec makeGroupBy 0,1,0 
-- exec makeGroupBy 0,1,1 
create proc makeGroupBy(@product_id  int = 0,@city_id  int = 1,@date_key  
int = 0) as 
begin
declare @tbl as table(product_id  int, city_id  int, date_key  int, amount                  
float)

insert into @tbl
values(1,1,1,10),
(1,1,1,10),
(1,2,1,5),
(2,2,3,15),
(2,1,3,20),
(3,1,1,25)

select case isnull(@product_id,0) when 0 then 0 else product_id end 
    ,case isnull(@city_id,0) when 0 then 0 else city_id end
    ,case isnull(@date_key,0) when 0 then 0 else date_key end
    , sum(amount) amount from @tbl 
group by case isnull(@product_id,0) when 0 then 0 else product_id end 
    ,case isnull(@city_id,0) when 0 then 0 else city_id end
    ,case isnull(@date_key,0) when 0 then 0 else date_key end
end

Я не знаю, возможно ли это, но я хочу, чтобы в результирующем наборе были опущены ненужные столбцы (входные данные со значением 0).

1. Вам нужен динамический SQL.

2. спасибо за ответ. даже для этого небольшого примера мне нужно будет сделать 6 разных запросов к тому, что я хочу. представьте, что количество столбцов равно 6 .. мне нужно будет выполнить 63 разных запроса.

3. он всегда выдает мне все три столбца, но, например, для «makeGroupBy 0,1,0» я не хочу, чтобы product_id и date_key были в моем наборе результатов

4. @samantarighpeima с Dynamic sql у вас будет только один запрос. Но я думаю, что это можно сделать с помощью grouping sets() и grouping() или grouping_id() .

Ответ №1:

Предполагая, что ваша sql-server версия больше или равна 2008 .

 select 
    product_id
    ,city_id
    ,date_key
    ,sum(amount) as total_amount 
from @tbl
group by grouping sets (
            (product_id, city_id, date_key)
            , (product_id,city_id)
            , (product_id, date_key)
            , (city_id, date_key)
            , (product_id)
            , (city_id)
            , (date_key))
having concat(iif(grouping_id(product_id)=0,1,0),iif(grouping_id(city_id)=0,1,0),iif(grouping_id(date_key)=0,1,0)) = concat(@product_id, @city_id, @date_key) 
order by concat(iif(grouping_id(product_id)=0,1,0),iif(grouping_id(city_id)=0,1,0),iif(grouping_id(date_key)=0,1,0))

Кажется, что view , вероятно, лучше всего подойдет для этого случая

 create view [view_name]
as 
select 
    product_id
    ,city_id
    ,date_key
    ,sum(amount) as amount 
    ,concat(iif(grouping_id(product_id)=0,1,0),iif(grouping_id(city_id)=0,1,0),iif(grouping_id(date_key)=0,1,0)) as grp_key
from @tbl
group by grouping sets (
        (product_id, city_id, date_key)
        , (product_id,city_id)
        , (product_id, date_key)
        , (city_id, date_key)
        , (product_id)
        , (city_id)
        , (date_key))
go

Затем вы можете запросить представление, подобное

 select 
    city_id
    ,date_key
    ,amount
from [view_name]
where grp_key = concat(0,1,1)

1. все еще получаю все столбцы в результирующем наборе. но есть другой способ сделать это, о котором я не знал .. большое вам спасибо.

2. @samantarighpeima Я рекомендую вам создать view или a table-valued function , чтобы вы могли запрашивать нужные столбцы из from предложения. Если stored procedure принудительно используется. Тогда это более вероятно, чем Dynamic SQL необходимость использования.

3. Кажется хорошей идеей попытаться объединить ваш метод с динамическим sql и функцией. . Я попытаюсь разобраться в этом .. спасибо, это было полезно

4. @samantarighpeima Спасибо, кажется, что view , вероятно, лучше всего подойдет для этого случая.

5. @samantarighpeima не кажется хорошей идеей объединять два метода. С помощью Dynamic SQL будут сгруппированы определенные столбцы, grouping sets будут сгруппированы все определенные группы столбцов, а затем объединены результирующие наборы. Следовательно, я могу разумно предположить, что Dynamic SQL это будет более эффективно.