BigQuery RANK () сразу по нескольким столбцам

У меня есть таблица в BigQuery следующей структуры:

my_table

name    num1    num2    num3    num4
joe     12      15      11      8
tom     15      18      13      11
bill    19      11      12      23
nick    27      13      16      15
sal      9      12      16      5
chris   13      19      25      23

и хотел бы создать 4 дополнительных столбца, каждый из которых выводит ранг 1 из 4 столбцов num *. Тогда моя цель:

name    num1    num2    num3    num4    num1_rk    num2_rk    num3_rk    num4_rk
joe     12      15      11      8       2          4          ...
tom     15      18      13      11      4          5
bill    19      11      12      23      5          1
nick    27      13      16      15      6          3
sal      9      12      16      5       1          2
chris   13      19      25      23      3          6

Я могу добиться следующего, применив функцию RANK() в вызове SELECT к каждому из 4 столбцов, однако это не идеально для моего варианта использования.

SELECT
  *,
  RANK() OVER (ORDER BY num1 ASC) AS num1_rank,
  RANK() OVER (ORDER BY num2 ASC) AS num2_rank,
  ...
FROM my_table

У меня очень широкая таблица с более чем 50 (и растущими) показателями, каждая из которых требует ранжирования. Есть ли способ сделать это, не применяя столбец PERCENT_RANK () более 50 раз?


google-bigquery
person Canovice    schedule 30.10.2019    source источник
comment
Я думаю, что лучший подход - преобразовать мою таблицу из широкого в длинный, затем разделить по различным метрикам при применении percent_rank, а затем преобразовать обратно в широкую.   -  person Canovice    schedule 30.10.2019

Ответы (1)


arrow_upward
2
arrow_downward

Ниже приведено самое близкое к тому, о чем вы просите

#standardSQL
SELECT * FROM my_table JOIN (
  SELECT name, STRING_AGG(CAST(num_rank AS STRING) ORDER BY OFFSET) ranks 
  FROM (
    SELECT name, OFFSET, RANK() OVER(PARTITION BY OFFSET ORDER BY CAST(num AS INT64)) AS num_rank
    FROM my_table t,
    UNNEST(SPLIT(REGEXP_REPLACE(FORMAT('%t', t), r'[() ]', ''))) num WITH OFFSET
    WHERE OFFSET > 0
    ORDER BY OFFSET
  ) GROUP BY name
) USING(name)

Если обратиться к образцу данных из вашего вопроса - вывод будет

Row name    num1    num2    num3    num4    ranks    
1   joe     12      15      11      8       2,4,1,2  
2   tom     15      18      13      11      4,5,3,3  
3   bill    19      11      12      23      5,1,2,5  
4   nick    27      13      16      15      6,3,4,4  
5   sal     9       12      16      5       1,2,4,1  
6   chris   13      19      25      23      3,6,6,5

Как вы можете видеть, приведенное выше не зависит от количества столбцов num - но ожидает, что они будут начинаться со второго - это можно настроить для любых реальных данных, которые у вас есть.

Кроме того, если вы хотите выводить ранги в виде массива вместо строки - вы можете использовать

ARRAY_AGG(num_rank ORDER BY OFFSET) ranks

вместо того

STRING_AGG(CAST(num_rank AS STRING) ORDER BY OFFSET) ranks

с выходом

введите здесь описание изображения

person Mikhail Berlyant    schedule 30.10.2019