Как соединить две таблицы и выбрать строку на основе значения (и второго по величине значения) в столбце?
У меня есть следующая таблица:
CREATE TABLE tableName
(
id INT,
customer VARCHAR(512),
region VARCHAR(512),
cost INT
);
INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'EMP', 'Europe', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'y', 'North America', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'y', 'North America', '60');
INSERT INTO tableName (id, customer, region, cost) VALUES ('1', 'z', 'South America', '90');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'z', 'Europe', '40');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'z', 'South America', '60');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'EMP', 'Middle East', '60');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'z', 'PACIFIC', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'a', 'PACIFIC', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('2', 'a', 'PACIFIC', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('3', 'EMP', 'Carribean', '90');
INSERT INTO tableName (id, customer, region, cost) VALUES ('3', 'EMP', 'Middle East', '70');
INSERT INTO tableName (id, customer, region, cost) VALUES ('3', 'k', 'South America', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('4', 'EMP', 'Africa', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('4', 'EMP', 'Central America', '80');
INSERT INTO tableName (id, customer, region, cost) VALUES ('4', 'EMP', 'Africa', '70');
Клиент может иметь несколько значений, таких как x, y, z, а также EMP.
Я хотел бы сгруппировать по идентификатору и получить клиента, у которого больше всего вхождений. Если большинство вхождений для клиента — это EMP, то я хотел бы получить второе наиболее распространенное значение. Если у клиента для определенного идентификатора есть только значения EMP, то он должен присутствовать в таблице. Это мой ожидаемый результат:
Я попробовал следующее: я выбрал только строки без EMP, получил самые высокие вхождения, а затем строки без EMP и их самые высокие вхождения. Но я не знаю, как правильно присоединиться к ним, так как получаю ошибку groupby.
Как я могу удалить повторяющиеся значения идентификатора, где клиент = «EMP», и сохранить одно из значений, где конкретный идентификатор имеет только значения EMP для своего столбца клиента?
Это то, что я пробовал:
select * from tableName where customer= 'EMP' group by id order by count(customer) desc
select * from tableName where customer!= 'EMP' group by id order by count(customer) desc
select q1.*
from (select * from tableName where customer !='EMP'
group by id order by count(customer) desc) q1
left outer join
(select * from tableName where customer = 'EMP' group by id order by count(customer ) desc) q2
on q1.id = q2.id
union
select q1.*
from (select * from tableName where customer ='EMP'
group by id order by count(customer) desc) q1
right outer join
(select * from tableName where customer= 'EMP' group by id order by count(customer) desc) q2
on q1.id != q2.id;
@Jens звучит так, будто они пытаются заставить его работать на обоих, скорее всего, цель не преследуется.
Для чего предназначено поле ID - его значения не уникальны. Должен ли «сотрудник» быть «именем таблицы» в вашем SQL? Что определяет «первую» строку - таблицы БД не имеют внутреннего порядка. Какое именно сообщение об ошибке вы получаете - это для использования * с GROUP BY? Зачем вообще нужно рассматривать все 100 столбцов для группировки?
Извините, я исправил имя таблицы. При использовании group by я получаю сообщение об ошибке «столбец должен отображаться в предложении GROUP BY или использоваться в агрегатной функции». Насколько я понимаю, если мне нужны все столбцы в конечном результате, когда я использовал предложение GROUP BY, они также должны отображаться в предложении GROUP BY (аналогично pandas).
Ответы 1
SELECT id, customer, region, cost
FROM (
SELECT id, customer, region, cost,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY CASE customer WHEN 'EMP' THEN 2 ELSE 1 END, count DESC) AS row_num
FROM (
SELECT id, customer, region, cost,
COUNT(*) OVER (PARTITION BY id, customer) AS count
FROM tableName
) AS t
) AS t
WHERE row_num = 1;
mysql или postgresql? удалите несвязанные теги