Andy Lanng Ответов: 2

Как мне сгруппировать tsql, заданный столбцом Flag bigint

Привет,

Я работаю над проектом, где эффективность функции поиска имеет решающее значение.

У меня есть несколько столбцов флагов (например, флаги перечисления в c#). Поиск по этим данным очень быстрый (3 миллисекунды туда и обратно), но теперь я пришел-а-Кроппер, и мне нужно выполнить подсчет групп.

Итак, у меня есть элемент "А", который содержит красный (1), Белый (8) и синий (64), поэтому столбец "цвета" содержит число 73.

Для поиска я могу искать предметы с красным цветом с помощью этого 
Declare @colour int
set @colour = 1
Select * from Items where (Colour & @colour) > 0


Это прекрасно работает. Теперь я должен сгруппировать его (тоже очень быстро)

таким образом, если у меня есть 8 элементов в общей сложности, 5 содержат красный, 3 содержат белый и 7 содержат синий, то результаты будут выглядеть следующим образом:
Colour      Qty
1           5
8           3
64          7
( Мне не нужно беспокоиться о названии )

Итак: есть ли какой-нибудь способ взять число 73 и побитово разделить его на группы?

(Часть 2: Как мне перевести это в Linq to SQL?)

Любые советы будут оценены по достоинству
Спасибо ^_^

Что я уже пробовал:

Попробовал написать логин в Linq, но забыл, что он не будет иметь перевода в TSQL.

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

2 Ответов

Рейтинг:
2

Kornfeld Eliyahu Peter 

SELECT VAL, COUNT(*)
FROM (
	SELECT 
		COLOR AS COLOR_VAL, 
		COLOR & 1 AS [1],
		COLOR & 2 AS [2],
		COLOR & 4 AS [4],
		COLOR & 8 AS [8],
		COLOR & 16 AS [16],
		COLOR & 32 AS [32],
		COLOR & 64 AS [64],
		COLOR & 128 AS [128]
	FROM (SELECT 73 AS COLOR UNION SELECT 53 UNION SELECT 91) AS COLORS) AS SUMMARY
UNPIVOT  
   (VAL FOR COLOR IN   
      ([1], [2], [4], [8], [16], [32], [64], [128])  
) AS UP
WHERE VAL > 0 
GROUP BY VAL


Жирная часть-это то, где вы фактически выбираете свои цветовые значения...

Andy Lanng

дело в том, что количество опций (флагов) находится где - то между 10 и 60 (следовательно, бигинты). Стоит ли это все еще за производительность, поворачивающую так много столбцов?

Kornfeld Eliyahu Peter

До тех пор, пока это фиксированное число значений... Вы должны поместить его в SP, чтобы он был предварительно скомпилирован, и это даст вам хорошую производительность...

Рейтинг:
14

Andy Lanng

Хорошо - я думаю, что нашел лучшее решение:

Я попробовал посмотреть с КТР:

with cte as (
	select cast(1 as bigint) as flag, 1 pow
	union all
	select POWER(cast(2 as bigint),pow), pow + 1
	from cte
	where flag < POWER(cast(2 as bigint),62)
)
, cte2 as (
	select flag from cte
	union select -9223372036854775808
)


но это было слишком медленно, так что теперь я превратил его в статическую таблицу. Я присоединяюсь к побитовому '&':
select Flag, Count(*)
From FlagValues fv 
inner join Items i on (fv.Flag & i.Colour)


Гораздо быстрее ^_^

Richard Deeming

В зависимости от данных и индексов вы можете вероятно получите немного лучшую производительность с помощью:

INNER JOIN Items i ON fv.Flag <= i.Colour And (fv.Flag & i.Colour) = fv.Flag

SQL не может индексировать & операция, но она должна иметь возможность использовать индекс на Colour колонка для удовлетворения <= присоединиться.