Skip to content

quantize_into_fixed_width_bins

quantize_into_fixed_width_bins(value, min_bound, max_bound, nb_bins)

Description

Get the bin_range in which belongs value with bins defined so that there are nb_bins bins of same width between min_bound and max_bound plus a bin ]-∞, min_bound[ and a bin ]max_bound, +∞[

Usage

Call or Deploy quantize_into_fixed_width_bins ?
Call quantize_into_fixed_width_bins directly

The easiest way to use bigfunctions

  • quantize_into_fixed_width_bins 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 quantize_into_fixed_width_bins in your project

Why deploy?

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

quantize_into_fixed_width_bins function can be deployed with:

pip install bigfunctions
bigfun get quantize_into_fixed_width_bins
bigfun deploy quantize_into_fixed_width_bins

Examples

select bigfunctions.eu.quantize_into_fixed_width_bins(-4, 0, 100, 10)

select bigfunctions.us.quantize_into_fixed_width_bins(-4, 0, 100, 10)

select bigfunctions.europe_west1.quantize_into_fixed_width_bins(-4, 0, 100, 10)

+-----------+
| bin_range |
+-----------+
| ]-∞, 0[   |
+-----------+


select bigfunctions.eu.quantize_into_fixed_width_bins(5, 0, 100, 10)

select bigfunctions.us.quantize_into_fixed_width_bins(5, 0, 100, 10)

select bigfunctions.europe_west1.quantize_into_fixed_width_bins(5, 0, 100, 10)

+-----------+
| bin_range |
+-----------+
| [0, 10[   |
+-----------+


select bigfunctions.eu.quantize_into_fixed_width_bins(97, 0, 100, 10)

select bigfunctions.us.quantize_into_fixed_width_bins(97, 0, 100, 10)

select bigfunctions.europe_west1.quantize_into_fixed_width_bins(97, 0, 100, 10)

+-----------+
| bin_range |
+-----------+
| [90, 100] |
+-----------+


select bigfunctions.eu.quantize_into_fixed_width_bins(130, 0, 100, 10)

select bigfunctions.us.quantize_into_fixed_width_bins(130, 0, 100, 10)

select bigfunctions.europe_west1.quantize_into_fixed_width_bins(130, 0, 100, 10)

+-----------+
| bin_range |
+-----------+
| ]100, +∞[ |
+-----------+

Use cases

Use Case: Customer Segmentation based on Purchase Value

An e-commerce company wants to segment its customers based on their total purchase value over the last year. They want to create 5 segments of equal width, ranging from the lowest purchase value to the highest.

Implementation with quantize_into_fixed_width_bins:

  1. Determine the minimum and maximum purchase values: 

    SELECT MIN(total_purchase_value) AS min_value, MAX(total_purchase_value) AS max_value
FROM customer_purchases;
    Let's assume min_value is 0 and max_value is 1000.

  2. Apply the quantize_into_fixed_width_bins function: 

    SELECT customer_id, total_purchase_value,
       bigfunctions.us.quantize_into_fixed_width_bins(total_purchase_value, 0, 1000, 5) AS purchase_segment
FROM customer_purchases;
    This will categorize each customer into one of the following segments:

  3. ]-∞, 0[ (unlikely in this case, as purchase value should be non-negative)

  4. [0, 200[
  5. [200, 400[
  6. [400, 600[
  7. [600, 800[
  8. [800, 1000]

  9. ]1000, +∞[

  10. Analyze and utilize the segments: The company can now use these segments for targeted marketing campaigns, personalized recommendations, and other business strategies. For example, customers in the highest segment ([800, 1000] and ]1000, +∞[) could receive exclusive offers or loyalty programs.

Benefits of using quantize_into_fixed_width_bins:

  • Simplified segmentation: Easily creates equally sized bins, making it straightforward to understand and interpret the segments.
  • Flexibility: The number of bins and the range can be adjusted to suit different segmentation needs.
  • Efficiency: The function handles the binning logic within the SQL query, eliminating the need for complex pre-processing steps.

Other Use Cases:

  • Categorizing website traffic: Segmenting users based on time spent on site, number of pages viewed, or other metrics.
  • Analyzing sensor data: Grouping sensor readings into bins for easier analysis and visualization.
  • Performance monitoring: Classifying response times or error rates into different severity levels.
  • Creating histograms: Generating histograms of data distributions using the binned values.
Need help or Found a bug?
Get help using quantize_into_fixed_width_bins

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about quantize_into_fixed_width_bins

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