根据发射月(min)给数据中心分配唯一的值？SQL问题

Question

我有一张表，显示每个月客户在数据中心的使用情况。我想根据最早的发射月份，为每个月(M1、M2、M3等)建立一个名为M1的专栏。

业务目标:我试图比较如何根据数据中心使用的第一个开始月份(M1)，等等.

样本数据：

+----------+------------+-----------+----------------------+
| Customer | Datacenter |   Month   | Total Credits Burned |
+----------+------------+-----------+----------------------+
| Apple    | DC1        | 12 / 2019 |                  618 |
| Google   | DC1        | 12 / 2019 |                  463 |
| Apple    | DC1        | 01 / 2019 |                  961 |
| Google   | DC1        | 01 / 2019 |                  590 |
| BCG      | DC5        | 11 / 2018 |                  729 |
| Mckinsey | DC5        | 11 / 2018 |                  194 |
| Bain     | DC5        | 11 / 2018 |                  652 |
| BCG      | DC5        | 12 / 2018 |                  952 |
| Mckinsey | DC5        | 12 / 2018 |                  477 |
| Bain     | DC5        | 12 / 2018 |                  917 |
| BCG      | DC5        | 01 / 2019 |                  618 |
| Mckinsey | DC5        | 01 / 2019 |                  350 |
| Bain     | DC5        | 01 / 2019 |                  378 |
| BCG      | DC5        | 02 / 2019 |                  302 |
| Mckinsey | DC5        | 02 / 2019 |                  838 |
| Bain     | DC5        | 02 / 2019 |                  500 |
+----------+------------+-----------+----------------------+

我试图获得以下输出：

+----------+------------+-----------+----------------------+--------------+
| Customer | Datacenter |   Month   | Total Credits Burned | Cohort_Month |
+----------+------------+-----------+----------------------+--------------+
| Apple    | DC1        | 12 / 2019 |                  618 | M1           |
| Google   | DC1        | 12 / 2019 |                  463 | M1           |
| Apple    | DC1        | 01 / 2019 |                  961 | M2           |
| Google   | DC1        | 01 / 2019 |                  590 | M2           |
| BCG      | DC5        | 11 / 2018 |                  729 | M1           |
| Mckinsey | DC5        | 11 / 2018 |                  194 | M1           |
| Bain     | DC5        | 11 / 2018 |                  652 | M1           |
| BCG      | DC5        | 12 / 2018 |                  952 | M2           |
| Mckinsey | DC5        | 12 / 2018 |                  477 | M2           |
| Bain     | DC5        | 12 / 2018 |                  917 | M2           |
| BCG      | DC5        | 01 / 2019 |                  618 | M3           |
| Mckinsey | DC5        | 01 / 2019 |                  350 | M3           |
| Bain     | DC5        | 01 / 2019 |                  378 | M3           |
| BCG      | DC5        | 02 / 2019 |                  302 | M4           |
| Mckinsey | DC5        | 02 / 2019 |                  838 | M4           |
| Bain     | DC5        | 02 / 2019 |                  500 | M4           |
+----------+------------+-----------+----------------------+--------------+

• 稍作编辑，从原来的帖子**感谢任何提示！

Answer 1

这回答了问题的原稿。

你似乎想要row_number()

select Datacenter, Month,
       row_number() over (partition by Datacenter order by Month) as cohort_month
from sampledata;