Skip to content

last_value

last_value(arr)

Description

Return last value of array (inspired from sql-snippets repo)

Usage

Call or Deploy last_value ?
Call last_value directly

The easiest way to use bigfunctions

  • last_value function is deployed in 39 public datasets for all of the 39 BigQuery regions.
  • It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region Dataset
eu bigfunctions.eu
us bigfunctions.us
europe-west1 bigfunctions.europe_west1
asia-east1 bigfunctions.asia_east1
... ...
Deploy last_value in your project

Why deploy?

  • You may prefer to deploy last_value in your own project to build and manage your own catalog of functions.
  • This is particularly useful if you want to create private functions (for example calling your internal APIs).
  • Get started by reading the framework page

Deployment

last_value function can be deployed with:

pip install bigfunctions
bigfun get last_value
bigfun deploy last_value

Examples

select bigfunctions.eu.last_value([1, 2, 3])

select bigfunctions.us.last_value([1, 2, 3])

select bigfunctions.europe_west1.last_value([1, 2, 3])

+-------+
| value |
+-------+
| 3     |
+-------+

Use cases

Imagine you have a table of customer orders, and each order has an array of timestamps representing different stages of the order fulfillment process (e.g., order placed, payment processed, shipped, delivered). You want to find the last timestamp in each array, which would represent the time the order was completed (delivered in this example).

SELECT
    order_id,
    bigfunctions.us.last_value(fulfillment_timestamps) AS order_completion_timestamp
FROM
    your_project.your_dataset.your_order_table

This query would use the last_value function to extract the last timestamp from the fulfillment_timestamps array for each order, giving you the order completion time.

Other use cases could include:

  • Finding the latest status update: If you have an array of status updates for a task or project, last_value can give you the most recent status.
  • Getting the last element of a sequence: If you have an array representing a sequence of events, last_value can retrieve the final event in the sequence.
  • Extracting the latest value from sensor readings: If you have an array of sensor readings over time, last_value can retrieve the most recent reading.

Essentially, anytime you need to efficiently extract the last element from an array within BigQuery, the last_value function provides a clean and easy solution.

Need help or Found a bug?
Get help using last_value

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about last_value

If the function does not work as expected, please

  • report a bug so that it can be improved.
  • or open the discussion with the community on Slack.

We also provide professional suppport.

Show your ❤ by adding a ⭐ on