Вопрос SQL для начинающих: запрос значков с золотыми и серебряными метками в Stack Exchange Data Explorer

Я использую Stack Exchange Data Explorer для изучения SQL, но я думаю, что основы этого вопроса применимы к другим базам данных.

Я пытаюсь запросить Badges таблица, которая в соответствии со Stexdex (так я буду называть ее теперь) имеет следующую схему:

  • Значки
    • Я бы
    • Идентификатор пользователя
    • название
    • Дата

Это хорошо работает для значков, таких как [Epic] а также [Legendary] которые имеют уникальные имена, но серебряные и золотые значки, специфичные для меток, похоже, смешаны вместе, имея одинаковое точное имя.

Вот пример запроса, который я написал для [mysql] тег:

SELECT
  UserId as [User Link],
  Date
FROM
  Badges
Where
  Name = 'mysql'
Order By
  Date ASC

Вывод (слегка аннотированный): как видно на stexdex:

User Link       Date                    
--------------- -------------------     // all for silver except where noted
Bill Karwin     2009-02-20 11:00:25     
Quassnoi        2009-06-01 10:00:16     
Greg            2009-10-22 10:00:25     
Quassnoi        2009-10-31 10:00:24     // for gold
Bill Karwin     2009-11-23 11:00:30     // for gold
cletus          2010-01-01 11:00:23    
OMG Ponies      2010-01-03 11:00:48     
Pascal MARTIN   2010-02-17 11:00:29 
Mark Byers      2010-04-07 10:00:35     
Daniel Vassallo 2010-05-14 10:00:38

Это согласуется с текущим списком лиц, получающих серебро и золото, на момент написания этой статьи, но, если говорить более вечно, по состоянию на конец мая 2010 года только 2 пользователя заработали золото [mysql] tag: Quassnoi и Bill Karwin, о чем свидетельствует приведенный выше результат, их имена являются единственными, которые появляются дважды.

Вот как я это понимаю:

  • Первый раз Id появляется (в хронологическом порядке) для серебряного значка
  • Второй раз за золото

Теперь, вышеупомянутый результат смешивает серебряные и золотые записи вместе. Мои вопросы:

  • Это типичный дизайн, или есть более дружелюбная схема / нормализация / как вы это называете?
  • В текущем дизайне, как бы вы запросили серебряные и золотые значки отдельно?
    • GROUP BY Id и выбирая мин / макс или первую / секунду Date каким-то образом?
    • Как вы можете написать запрос, в котором сначала перечислены все серебряные значки, а затем все золотые значки?
      • Представьте также, что "реальный" запрос может быть более сложным, то есть не просто перечислением по дате.
      • Как бы вы написали так, чтобы между серебряными и золотыми подзапросами не было слишком много повторений?
    • Возможно, более типично вместо этого делать два совершенно разных запроса?
    • Как называется эта идиома? Строка "разбиение" запроса, чтобы поместить их в "ведра" или что-то?

Требование разъяснения

Первоначально я хотел следующий вывод, по существу:

User Link       Date                    
--------------- -------------------     
Bill Karwin     2009-02-20 11:00:25     // result of query for silver
Quassnoi        2009-06-01 10:00:16     // :
Greg            2009-10-22 10:00:25     // :
cletus          2010-01-01 11:00:23     // :
OMG Ponies      2010-01-03 11:00:48     // :
Pascal MARTIN   2010-02-17 11:00:29     // :
Mark Byers      2010-04-07 10:00:35     // :
Daniel Vassallo 2010-05-14 10:00:38     // :
------- maybe some sort of row separator here? can SQL do this? -------
Quassnoi        2009-10-31 10:00:24     // result of query for gold
Bill Karwin     2009-11-23 11:00:30     // :

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

Источник

2 ответа

Решение

Это типичный дизайн, или есть более дружелюбная схема / нормализация / как вы это называете?

Конечно, вы можете добавить код типа, чтобы сделать его более явным. Но если учесть, что нельзя получить золотой значок раньше, чем серебряный, отметка даты имеет большой смысл различать между ними.

В текущем дизайне, как бы вы запросили серебряные и золотые значки отдельно? GROUP BY Id и выбор минимальной / максимальной или первой / второй по дате как-нибудь?

Да - присоединение к производной таблице (встроенное представление AKA), которая представляет собой список пользователей, и минимальная дата выдаст серебряные значки. С помощью HAVING COUNT(*) >= 1 будет работать тоже. Вам нужно будет использовать комбинацию GROUP BY и HAVING COUNT(*) = 2`, чтобы получить золотые значки - максимальная дата не гарантирует, что для идентификатора пользователя существует более одной записи...

Как вы можете написать запрос, в котором сначала перечислены все серебряные значки, а затем все золотые значки?

Извините - пользователи или все серебро сначала, а потом золото? Первое может быть сделано просто с помощью ORDER BY t.userid, t.date; последний я бы, вероятно, использовал аналитические функции (IE: ROW_NUMBER(), RANK())...

Возможно, более типично вместо этого делать два совершенно разных запроса?

Смотри выше о том, насколько расплывчаты твои требования, для меня в любом случае...

Как называется эта идиома? Строка "разбиение" запроса, чтобы поместить их в "ведра" или что-то?

То, о чем вы спрашиваете, обозначается следующими синонимами: аналитика, управление окнами, ранжирование...

Источник

Вы будете делать что-то подобное и полагаться только на дату или рассчитывать в совокупности.

Возможно, также нет смысла запрашивать серебро, за которым следует золото, а скорее получать данные рядом, как это:

К сожалению, вы на самом деле не указали, что вы хотите, но хорошей отправной точкой для агрегатов является выражение на простом английском языке.

Пример: "Дайте мне даты серебряных и золотых наград за каждого пользователя для тега MySQL". Что это делает:

SELECT
  UserId as [User Link],
  min(Date) as [Silver Date],
  case when count(*) = 1 THEN NULL ELSE max(date) END
FROM
  Badges
Where
  Name = 'mysql'
group by
  UserId
Order By
  case when count(*) = 1 THEN NULL ELSE max(date) END DESC, min(Date)

Изменить, после обновления:

Ваш желаемый результат на самом деле не SQL: это 2 отдельных набора записей. Разделитель запрещён. В качестве операции на основе setb "естественного" порядка не существует, поэтому он вводит один:

SELECT
  UserId as [User Link],
  min(Date) as [Date],
  0 as dummyorder
FROM
  Badges
Where
  Name = 'mysql'
group by
  UserId
union all
select
  UserId as [User Link],
  max(Date) as [Date],
  1 as dummyorder
FROM
  Badges
Where
  Name = 'mysql'
group by
  UserId
having
  count(*) = 2
Order By
  dummyorder, Date
Источник
Другие вопросы по тегам