时间戳

首页 > 解决方案 > 在 Bigquery 中获取下一个值作为当前值

sql - 在 Bigquery 中获取下一个值作为当前值

问题描述

我有这样的数据：

+-----+------------------+--------------------------+------------+
| 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      |
+-----+------------------+--------------------------+------------+

我想获得前一个order_id并将其转换date_order为我的时区。所以我做了这样的查询：

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

我得到的结果是这样的：

+-----+------------------+-------------+------------+------------------+
| 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             |
+-----+----------------- +-------------+------------+------------------+

我想要的结果是这样的：

+-----+------------------+-------------+------------+------------------+
| 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             |
+-----+-----------------+--------------+------------+------------------+

如果order_id等于prev_order，我想获取prev_order列中的前一个值。

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 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

如果适用于您的问题的样本数据 - 结果是 

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

推荐阅读