[bigquery]特定の数値でランキングをつけて、さらに100分率を算出するコード

2023-12-07 2023-12-06

SELECT

product,

value,

RANK() OVER (PARTITION BY product ORDER BY value DESC) AS ranking,

NTILE(100) OVER (PARTITION BY product ORDER BY value DESC) AS percentile_rank

FROM

dataset.table

ORDER BY

1, 2, 3, 4

SELECT product, value, RANK() OVER (PARTITION BY product ORDER BY value DESC) AS ranking, NTILE(100) OVER (PARTITION BY product ORDER BY value DESC) AS percentile_rank FROM dataset.table ORDER BY 1, 2, 3, 4

SELECT
  product,
  value,
  RANK() OVER (PARTITION BY product ORDER BY value DESC) AS ranking,
  NTILE(100) OVER (PARTITION BY product ORDER BY value DESC) AS percentile_rank
FROM
  dataset.table
ORDER BY
  1, 2, 3, 4