find_lower_value¶

find_lower_value(arr, x)

Description¶

Return the offset (zero-based index) of the first value in arr where value <= x (or null if no value is lower than x).

Usage¶

Call or Deploy find_lower_value ?

Call find_lower_value directly

The easiest way to use bigfunctions

find_lower_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 find_lower_value in your project

Why deploy?

You may prefer to deploy find_lower_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

find_lower_value function can be deployed with:

pip install bigfunctions
bigfun get find_lower_value
bigfun deploy find_lower_value

Examples¶

1. When a strictly lower value exists in array

EUUSeurope-west1

select bigfunctions.eu.find_lower_value([5, 4, 3, 100], 3.5)

select bigfunctions.us.find_lower_value([5, 4, 3, 100], 3.5)

select bigfunctions.europe_west1.find_lower_value([5, 4, 3, 100], 3.5)

+--------+
| offset |
+--------+
| 2      |
+--------+

2. When an identical value exists in array

EUUSeurope-west1

select bigfunctions.eu.find_lower_value([5, 4, 3, 100], 4)

select bigfunctions.us.find_lower_value([5, 4, 3, 100], 4)

select bigfunctions.europe_west1.find_lower_value([5, 4, 3, 100], 4)

+--------+
| offset |
+--------+
| 1      |
+--------+

3. When a lower value does NOT exist in array

EUUSeurope-west1

select bigfunctions.eu.find_lower_value([5, 4, 3, 100], 2)

select bigfunctions.us.find_lower_value([5, 4, 3, 100], 2)

select bigfunctions.europe_west1.find_lower_value([5, 4, 3, 100], 2)

+--------+
| offset |
+--------+
| null   |
+--------+

Use cases¶

Imagine you have a table of product prices and you want to find the index of the first product in a given list that is priced at or below a certain threshold.

Scenario: You're building a price comparison tool. You have a table with competitor prices for a specific product:

Competitor	Price
A	12.99
B	10.50
C	15.00
D	9.99
E	11.75

A user sets a maximum price they are willing to pay, say $11.00. You want to quickly find the first competitor in the list offering a price at or below $11.00.

BigQuery SQL using find_lower_value:

SELECT find_lower_value([12.99, 10.50, 15.00, 9.99, 11.75], 11.00) AS first_affordable_competitor_index;

This query would return 1, which is the index of competitor B (remember, it's zero-based indexing).

Other Use Cases:

Inventory Management: Finding the index of the first bin in a warehouse with stock at or below a reorder point.
Data Analysis: Quickly locating the first data point in a time series that falls below a certain threshold (e.g., first day temperature dropped below freezing).
Game Development: Determining the first item in a sorted list of player scores that is less than or equal to a given score.
Algorithm Optimization: As part of more complex algorithms like binary search variations, where you need to efficiently find the position of an element or the first element meeting a specific condition within a sorted or partially sorted array.

This function provides a concise and efficient way to perform this type of search within BigQuery without needing to write more complex procedural code.

Need help or Found a bug?

Get help using find_lower_value

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about find_lower_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