Google BigQuery Dimensions SQL Multiple Grids

how would you select multiple grids in the example below instead of just one under “WHERE”:

SELECT
    sdg_code,
    sdg_name,
    "grid.5170.3" as grid,
    year,
    COUNT(DISTINCT id) as pubs,
    ROUND(AVG(fcr), 1) as fcr,
    ROUND(EXP(AVG(LOG(GREATEST(fcr, 1)))), 1) as fcr_geomean,
    ROUND(sum(AltWithScore), 1) as altmetric
FROM
    (
        SELECT
            p.id,
            year,
            if(p.altmetrics.score > 0, 1, 0) as AltWithScore,
            cat_sdg.code as sdg_code,
            cat_sdg.name as sdg_name,
            p.metrics.field_citation_ratio as fcr,
            p.altmetrics.score as altmetric_score,
            row_number() over(partition by p.id, cat_sdg.code) as rn
        FROM
            dimensions - ai.data_analytics.publications p,
            UNNEST(category_sdg.full) cat_sdg
        WHERE
            year >= 2011
            AND year <= 2020
            AND "grid.5170.3" in UNNEST(research_orgs)
    )
WHERE rn = 1
GROUP BY
    sdg_code,
    sdg_name,
    year
ORDER BY year asc

What needs to be changed:

It currently only runs for 1 organisation (GRID), I would like it to run for 11 organisations. The org is identified with an ID called a “GRID”, it looks like this: “grid.5170.3”

I want my new code to take 10 org IDs more. Need to use these 10: grid.5254.6, grid.7048.b, grid.5117.2, grid.10825.3e, grid.4655.2, grid.11702.35, grid.154185.c, grid.475435.4, grid.7143.1, grid.27530.33

And add a column with the org name; currently there is only a column with the org.

Thanks, new to this whole thing.

Answer

Instead of WHERE year >= 2011 AND year <= 2020 AND "grid.5170.3" in UNNEST(research_orgs) use below

WHERE year >= 2011 AND year <= 2020 
AND EXISTS (
  SELECT 1
  FROM UNNEST(research_orgs) grid 
  WHERE grid IN ('grid.5170.3', 'grid.5254.6', 'grid.7048.b', 'grid.5117.2', 'grid.10825.3e', 'grid.4655.2', 'grid.11702.35', 'grid.154185.c', 'grid.475435.4', 'grid.7143.1', 'grid.27530.33')
)

Advertisement

Answer