Loading...

Getting the next value as current value in Bigquery


I have data like this

Row order_id    date_order  partner_id  
1   SNB1905/0007786 2019-05-15 10:17:57 UTC 70959
2   SNB1905/0007786 2019-05-15 10:17:56 UTC 70959
3   SNB1904/0008810 2019-04-30 07:18:11 UTC 70959
4   SNB1902/0003122 2019-02-20 07:05:00 UTC 70959

I want to get the previous order_id and also convert the date_order to my timezone. So I've done query like this

SELECT
  order_id,
  DATE(date_order,"Asia/Jakarta") AS date_only,
  partner_id,
  LAG(origin,1) OVER(PARTITION BY partner_id order by date_order) prev_order
FROM
  `my_table`
WHERE
  partner_id = 70959
ORDER BY
  date_order DESC

The result that I get is like this

Row order_id    date_only   partner_id  prev_order  
1   SNB1905/0007786 2019-05-15 70959 SNB1904/0008810
2   SNB1905/0007786 2019-05-15 70959 SNB1905/0007786
3   SNB1904/0008810 2019-04-30 70959 SNB1902/0003122
4   SNB1902/0003122 2019-02-20 70959 null

The result that I want is like this

Row order_id  date_only partner_id  prev_order  
1   SNB1905/0007786 2019-05-15 70959 SNB1904/0008810
2   SNB1905/0007786 2019-05-15 70959 SNB1904/0008810
3   SNB1904/0008810 2019-04-30 70959 SNB1902/0003122
4   SNB1902/0003122 2019-02-20 70959 null

If the order_id is equal to prev_order, I want to get the previous value in prev_order column.

Please help me to solve this. Thank you in advanced.

- - Source

Answers

answered 1 week ago Mikhail Berlyant #1

Below is for BigQuery Standard SQL

#standardSQL
SELECT
  order_id,
  date_only,
  partner_id,  
  ( SELECT order_id FROM UNNEST(prev_orders) prev_order
    WHERE t.order_id != order_id
    ORDER BY date_order DESC LIMIT 1
  ) prev_order
FROM (
  SELECT
    order_id,
    date_order,
    DATE(date_order,"Asia/Jakarta") AS date_only,
    partner_id,
    ARRAY_AGG(STRUCT(order_id AS order_id, date_order AS date_order)) 
      OVER(PARTITION BY partner_id ORDER BY date_order ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) prev_orders
  FROM `project.dataset.table`
) t
WHERE partner_id = 70959
-- ORDER BY date_order DESC

if to apply to sample data from your question - result is

Row order_id        date_only   partner_id  prev_order   
1   SNB1905/0007786 2019-05-15  70959       SNB1904/0008810  
2   SNB1905/0007786 2019-05-15  70959       SNB1904/0008810  
3   SNB1904/0008810 2019-04-30  70959       SNB1902/0003122  
4   SNB1902/0003122 2019-02-20  70959       null     

comments powered by Disqus