У меня есть таблица с:
id | parameter
1 | A
1 | B
2 | A
3 | A
3 | B
Это представляет объекты, определенные со значениями как:
1 -> A,B
2 -> A
3 -> A,B
Я хочу считать количество объектов с различными параметрами с помощью SQL-запроса, так же в этом случае это были бы 2 уникальных объекта, как 1 и 3 имеют те же параметры.
Нет никакого ограничения на количество параметров, может быть 0, или любое другое число.
База данных является Microsoft SQL Server 2000. Но я не возражаю знать решение для других баз данных.
Я решил проблему с помощью ссылки, предоставленной Cheran S (поскольку Microsoft SQL Server все еще не имеет функции GROUP_CONCAT()) http://dataeducation.com/rowset-string-concatenation-which-method-is-best/
Вы можете использовать предложение с предложением
для фильтрации по двум уникальным параметрам:
select count(*)
from YourTable
group by
id
having count(distinct parameter) > 1
Если я правильно понимаю, вам нужно количество различных комбинаций из параметра
s на id
, представленных в вашей таблице, возможно, с количеством объектов, показывающих каждая из этих различных комбинаций.
Я не могу говорить о SQL Server, но в MySQL вы можете сделать что-то вроде этого:
SELECT parameter_set, COUNT(*) AS entity_count
FROM (
-- Here we "flatten" the different parameter combinations per id
SELECT id,
GROUP_CONCAT(parameter ORDER BY parameter) AS parameter_set
FROM tbl
GROUP BY id
) d
GROUP BY parameter_set;
, что даст вам следующее:
parameter_set | entity_count
---------------+--------------
A,B | 2 -- two entities have params A, B
A | 1 -- one entity has param A
и SELECT COUNT (DISTINCT parameter_set FROM (... сглаживающий запрос ...)) d
даст вам количество различных наборов параметров.
Хорошо, вот моя попытка. Возможно, удастся реализовать эту логику таким образом, чтобы не требовалось 5 обращений к одной и той же таблице, но я не могу думать об этом прямо сейчас.
Логика здесь состоит в том, чтобы сначала удалить повторяющиеся объекты, а затем подсчитать оставшиеся идентификаторы. Подзапрос NOT IN
представляет объекты, у которых есть соответствующий объект с меньшим идентификатором. Подзапрос объединяет параметры двух объектов t1 и t2, затем подсчитывает, сколько параметров совпало для каждой пары t1 / t2. Если количество совпадающих параметров совпадает с количеством параметров в t1 и t2, тогда t2 и t1 совпадают, и мы должны исключить t1 из набора результатов.
DECLARE @tab TABLE (ID int, parameter varchar(2));
INSERT INTO @tab
SELECT 1, 'A' UNION ALL
SELECT 1, 'B' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 3, 'A' UNION ALL
SELECT 3, 'B' UNION ALL
SELECT 4, 'A' UNION ALL
SELECT 5, 'C' UNION ALL
SELECT 5, 'D';
SELECT
COUNT(DISTINCT t.ID) AS num_groups
FROM
@tab AS t
WHERE
t.ID NOT IN
(SELECT
t1.ID AS ID1
FROM
@tab AS t1
INNER JOIN
@tab AS t2
ON
t1.ID > t2.ID AND
t1.parameter = t2.parameter
GROUP BY
t1.ID,
t2.ID
HAVING
COUNT(*) = (SELECT COUNT(*) FROM @tab AS dupe WHERE dupe.ID = t1.ID) AND
COUNT(*) = (SELECT COUNT(*) FROM @tab AS dupe WHERE dupe.ID = t2.ID)
);
Результат на SQL Server 2008 R2:
num_groups
3
Что касается объектов с 0 параметрами, это зависит от того, как они хранятся, но обычно вам просто нужно добавить один к приведенному выше ответу, если есть какие-либо объекты с 0 параметров.
В SQL Server 2000 нет надежного способа сделать это с заданными условиями, но следующий способ будет работать в большинстве ситуаций и предупредит вас, если он не сработает.
Дана таблица "tbl"
ID Parameter
1 A
1 B
2 A
3 A
3 B
4 A
4 NULL
5 C
5 D
6 NULL
.
Создайте эту функцию:
CREATE FUNCTION MakeParameterListFor_tblID (@ID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE
@ParameterList VARCHAR(8000),
@ListLen INT
SET
@ParameterList = ''
SELECT
@ParameterList = @ParameterList + COALESCE (Parameter, '*null*') + ', '
FROM
tbl
WHERE
ID = @ID
ORDER BY
Parameter
SET @ListLen = LEN (@ParameterList)
IF @ListLen > 7800 -- 7800 is a SWAG.
SET @ParameterList = '*Caution: overflow!*' + @ParameterList
ELSE
SET @ParameterList = LEFT (@ParameterList, @ListLen-1) -- Kill trailing comma.
RETURN @ParameterList
END
GO
.
Затем этот запрос:
SELECT
COUNT (ID) AS NumIDs,
NumParams,
ParamList
FROM
(
SELECT
ID,
COUNT (Parameter) AS NumParams,
dbo.MakeParameterListFor_tblID (ID) AS ParamList
FROM
tbl
GROUP BY
ID
) AS ParamsByID
GROUP BY
ParamsByID.ParamList,
ParamsByID.NumParams
ORDER BY
NumIDs DESC,
NumParams DESC,
ParamList ASC
.
Выдаст то, что вы просили.
Результаты:
NumIDs NumParams ParamList
2 2 A, B
1 2 C, D
1 1 *null*, A
1 1 A
1 0 *null*