BigQuery/SQL - Remove duplicates from column for specified parameters

Question

I need to remove duplicates from one column witch specified parameters. https://i.sstatic.net/V3tDZ.png

need to replace duplicates in column market_offers with nulls and leave only one value but there is a case that there are different rank so i need to leave unique values for each rank and country_code https://i.sstatic.net/75EMj.png Bigquery SQL

result

country_code    rank    store_id    category_id offers  market_offers
se              1       14582       1106        410     504860
se              1       1955        1294        2       504860
se              1       9831        1158        151     504860
se              2       666         11158       536     4000
se              2       6587        25863       6586    4000
se              2       6666        158         536     4000
se              5       65853       76722       1521    302
se              5       6587        25863       6586    302

expected result

country_code    rank    store_id    category_id offers  market_offers
se              1       14582       1106        410     504860
se              1       1955        1294        2       null
se              1       9831        1158        151     null
se              2       666         11158       536     4000
se              2       6587        25863       6586    null
se              2       6666        158         536     null
se              5       65853       76722       1521    302
se              5       6587        25863       6586    null

SELECT
  country_code,
  rank,
  store_id,
  store_name,
  category_id,
  category_name,
  offers,
  main_category_id,
  main_category,
  market_offers,
  FROM
  (SELECT
    country_code,
    rank,
    store_id,
    store_name,
    category_id,
    category_name,
    offers,
    main_category_id,
    main_category,
    SUM(offers) OVER (PARTITION BY country_code, rank) AS market_offers,
    -- SUM(offers) OVER (PARTITION BY store_id,rank) AS store_offers,
    -- SUM(offers) OVER (PARTITION BY category_id,rank ) AS category_offers,
    -- SUM(offers) OVER (PARTITION BY main_category_id ,rank) AS segment_offers
  FROM (
    SELECT
      country_code,
      rank,
      store_id,
      store_name,
      category_id,
      category_name,
      COUNT(price_id ) AS offers,
      main_category_id,
      main_category,
    FROM
      `bigquery-kpi-engine.cba.price_position`
    WHERE
      featured_status = "NO"
      -- and store_id = 35714
      AND country_code = "se"
    GROUP BY
      country_code,
      rank,
      store_id,
      store_name,
      category_id,
      category_name,
      featured_status,
      main_category_id,
      main_category )) AS main

Could you paste an example of input data and desired output? — Sergey Geron
– Sergey Geron, Commented Apr 2, 2021 at 12:51

Mikhail Berlyant · Accepted Answer · 2021-04-02 15:51:49Z

1

Consider below

select * except(market_offers), 
  if(
    row_number() over(partition by market_offers, rank) = 1, 
    market_offers, 
    null
  ) as market_offers
from `project.dataset.table`

if applied to sample data in your question - output is

answered Apr 2, 2021 at 15:51

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lukas Over a year ago

One more thing...as i'm trying to put this result to DataStudio i'm getting another problem so, is this possible to to make it in another way... i'm thinking about split number for unique rank in country by number of entries? so for rank 5 from data above should be 151, for rank 2 - 1333 etc...

Mikhail Berlyant Over a year ago

Sure. Your original question is fully answered. Please post new question with all relevant details and I will be happy to help :o)

Lukas Over a year ago

here is new post :) stackoverflow.com/questions/66960676/… and thanks for your help :)

Collectives™ on Stack Overflow

BigQuery/SQL - Remove duplicates from column for specified parameters

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related