sql - 初学者SQL问题: querying gold and silver tag badges in Stack Exchange Data Explorer

Question

我正在使用 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

(略有注释的)输出是:as seen on 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 

这与当前列表silver一致和 gold截至 2010 年 5 月底，只有 2 名用户获得了金 [mysql] 标签:Quassnoi 和 Bill Karwin，如上面的结果是他们的名字是唯一出现两次的名字。

我是这样理解的:

• Id 第一次出现(按时间顺序)是为了银徽章
• 第二次是为了金牌

现在，上面的结果将白银和黄金条目混合在一起。我的问题是:

• 这是一个典型的设计，还是有更友好的模式/规范化/无论你怎么调用它？
• 在目前的设计中，您将如何分别查询银徽章和金徽章？
  • GROUP BY Id 并以某种方式选择 Date 的最小值/最大值或第一个/第二个？
  • 您如何编写一个查询，先列出所有银色徽章，然后再列出所有金色徽章？
    • 再想象一下“真正的”查询可能更复杂，即不只是按日期列出。
    • 您将如何编写它以使其在 silver 和 gold 子查询之间没有太多重复？
  • 执行两个完全独立的查询可能更典型吗？
  • 这个成语叫什么？将它们放入“桶”之类的行“分区”查询？

---

需求说明

最初我想要以下输出，本质上是:

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     // :

但到目前为止，对于白银和黄金的单独列的答案也很好，所以也可以随意追求这个角度。不过，我仍然很好奇您将如何执行上述操作。

Answer 1

Is this a typical design, or are there much friendlier schema/normalization/whatever you call it?

当然，您可以添加类型代码以使其更明确。但是当你考虑到一个人不能在获得银徽章之前获得金徽章时，日期戳就很适合区分它们。

In the current design, how would you query the silver and gold badges separately? GROUP BY Id and picking the min/max or first/second by the Date somehow?

是的 - 加入派生表(也称为内联 View )，该表是用户列表和最短日期将返回银徽章。使用 HAVING COUNT(*) >= 1 也可以。您必须结合使用 GROUP BY 和 HAVING COUNT(*) = 2` 才能获得金徽章 - 最大日期不能确保用户 ID 有多个记录...

How can you write a query that lists all the silver badges first then all the gold badges next?

抱歉 - 用户，还是先是银牌再是金牌？前者可以简单地通过使用 ORDER BY t.userid, t.date 来完成；后者我可能会使用分析函数(即:ROW_NUMBER()、RANK())...

Is it perhaps more typical to do two totally separate queries instead?

请参阅上面关于您的要求有多模糊，无论如何对我...

What is this idiom called? A row "partitioning" query to put them into "buckets" or something?

您所问的内容由以下同义词指代:Analytic、Windowing、ranking...