CODEWITHSUNDEEP
google-bigquerylooker-studio
Mim
Mim

Reputation: 1069

how to replicate row_number in BigQuery BI engine

I have this Query which works fine in BigQuery

WITH
  tt AS (
  SELECT
    StationName,
    SUM(Mwh) AS Mwh
  FROM
    `datastudio.today_view_MT`
  GROUP BY
    1)
SELECT
  StationName,
  Mwh,
  ROW_NUMBER() OVER(ORDER BY Mwh DESC ) AS rnk
FROM
  tt

but as of August 2020, BI engine does not accelerate analytics functions, how to rewrite the same query using self join or somthing like that so it will be accelerated by BI engine.

My real use case, the query uses parameters from Data Studio, so I can not materialized the results, there are three parameters, so a lot of different combinations.

Upvotes: 0

Views: 854

Answers (2)

Thomas
Thomas

Reputation: 577

As requested this seems to work:

WITH
  tt AS (
  SELECT
    "A" AS fixed_value,
    StationName,
    SUM(Mwh) AS Mwh
  FROM
    `datastudio.today_view_MT`
  GROUP BY
    1, 2)
SELECT
  tt.StationName,
  tt.Mwh,
  COUNT(*) AS row_number
FROM
  tt
JOIN
  tt tt2
ON
  tt2.fixed_value = tt.fixed_value
  AND tt2.Mwh <= tt.Mwh
GROUP BY
  tt.fixed_value,
  tt.Mwh,
  tt.StationName
ORDER BY
  row_number

Please be aware that you could have duplicate row numbers

Upvotes: 1

Thomas
Thomas

Reputation: 577

I've tried using joins but I never got the correct results so here is my out of the box solution:

WITH
  tt AS (
  SELECT
    StationName,
    SUM(Mwh) AS Mwh
  FROM
    `datastudio.today_view_MT`
  GROUP BY
    1),
  structs AS (
  SELECT
    STRUCT(StationName,
      Mwh) AS station_struct
  FROM
    tt ),
  combinations AS (
  SELECT
    ARRAY_AGG(station_struct) AS stations,
    GENERATE_ARRAY(1,(
      SELECT
        COUNT(*)
      FROM
        tt)) AS numbers
  FROM
    structs )
SELECT
  station.StationName,
  station.Mwh,
  numbers[
OFFSET
  (stations_offset)] AS number
FROM
  combinations,
  UNNEST(stations) AS station
WITH
OFFSET
  AS stations_offset

Now the question is can you use generate_arrays and offsets in BI engine?

Upvotes: 1

Related Questions