J'utilise BigQuery et j'essaie d'attribuer des valeurs catégorielles à chacun de mes enregistrements, en fonction du nombre de "fractionnements" qui lui sont attribués.
La table comporte un nombre cumulé d'enregistrements, regroupés au niveau STR - c'est-à-dire que s'il y a 4 SKU à 2 STR, les SKU seront étiquetés 1,2,3,4. Chaque STR se voit attribuer une valeur SPLIT, donc si la STR a une valeur SPLIT de 2, je veux qu'elle divise ses SKU en 2 catégories. Je souhaite créer une autre colonne qui attribuerait les SKU étiquetés 1-2 comme «1» et les SKU étiquetés 3-4 comme «2». (Les données réelles sont à une échelle beaucoup plus grande, mais je pensais que ce serait plus facile.)
+-----+------+---------------+--------+-------------+ | STR | SKU | SKU_ROW_COUNT | SPLITS | HOST_NUMBER | +-----+------+---------------+--------+-------------+ | 1 | 1230 | 1 | 3 | 1 | | 1 | 1231 | 2 | 3 | 1 | | 1 | 1232 | 3 | 3 | 2 | | 1 | 1233 | 4 | 3 | 2 | | 1 | 1234 | 5 | 3 | 3 | | 1 | 1235 | 6 | 3 | 3 | | 2 | 1310 | 1 | 2 | 1 | | 2 | 1311 | 2 | 2 | 1 | | 2 | 1312 | 3 | 2 | 2 | | 2 | 1313 | 4 | 2 | 2 | | 3 | 2345 | 1 | 1 | 1 | | 3 | 2346 | 2 | 1 | 1 | | 3 | 2347 | 3 | 1 | 1 | +-----+------+---------------+--------+-------------+
La colonne SPLITS est dynamique, allant de 1 à 3. Le nombre de SKU dans chaque catégorie devrait être relativement égal, mais ce n'est pas une priorité autant que le nombre de groupes qui sont créés. Idéalement, la table finale avec la nouvelle colonne (HOST_NUMBER) ressemblerait à ceci:
+-----+------+---------------+--------+ | STR | SKU | SKU_ROW_COUNT | SPLITS | +-----+------+---------------+--------+ | 1 | 1230 | 1 | 3 | | 1 | 1231 | 2 | 3 | | 1 | 1232 | 3 | 3 | | 1 | 1233 | 4 | 3 | | 1 | 1234 | 5 | 3 | | 1 | 1235 | 6 | 3 | | 2 | 1310 | 1 | 2 | | 2 | 1311 | 2 | 2 | | 2 | 1312 | 3 | 2 | | 2 | 1313 | 4 | 2 | | 3 | 2345 | 1 | 1 | | 3 | 2346 | 2 | 1 | | 3 | 2347 | 3 | 1 | +-----+------+---------------+--------+
3 Réponses :
Vous pouvez utiliser les fonctions de fenêtre et l'arithmétique:
select
t.*,
ntile(splits) over(partition by str order by sku) host_number
from mytable t
order by sku
En fait, ntile () semble faire exactement ce que vous voulez - et vous n'avez même pas besoin du Colonne sku_row_count (qui imite de toute façon row_number () ):
select
t.*,
1 + floor((sku_row_count - 1) * splits / count(*) over(partition by str)) host_number
from mytable t
order by sku
Si l'ordre des valeurs dans les groupes n'a pas d'importance, utilisez simplement l'arithmétique modulo:
select t.*, (SKU_ROW_COUNT % SPLITS) as split_group from t
Ci-dessous, pour BigQuery Standard SQL
#standardSQL SELECT *, 1 + MOD(SKU_ROW_COUNT, SPLITS) AS HOST_NUMBER FROM `project.dataset.table`