Скачать или смотреть Calculating the Median Price in BigQuery

Learn how to effectively compute the `median price` for different products, including `apples`, using BigQuery SQL with this clear and comprehensive guide.
---
This video is based on the question https://stackoverflow.com/q/68299774/ asked by the user 'Mark' ( https://stackoverflow.com/u/5055794/ ) and on the answer https://stackoverflow.com/a/68299827/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: In BigQuery Standard SQL how to get a median of a subet of rows

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Median Calculation in BigQuery

When working with datasets in BigQuery, one common challenge is extracting valuable insights from your data. A frequent requirement is to calculate medians from a dataset. For example, many analysts or businesses might want to determine the median price of products sold over different days or specifically for a certain type of product, such as apples. In this post, we'll explore how to do this effectively using BigQuery's SQL capabilities.

The Dataset

To illustrate our solution, let’s start with a sample dataset that includes day-wise product sales with their corresponding prices. Here's how the data looks:

DayProductPriceSunApples10SunApples12SunApples13SunOranges15SunOranges16MonApples10MonOranges14MonOranges15Given this dataset, we want to derive two median values for each day:

The overall median price

The median price specifically for apples

The expected output would resemble this table:

DayMedian OverallMedian ApplesSun1310Mon1410Solution: Using percentile_cont()

To achieve this median calculation for each day, we can utilize the percentile_cont() function. This function is designed to compute continuous percentiles effectively. Below is a breakdown of how to implement it in your SQL query.

SQL Query Explanation

Here’s the SQL query that you can use:

[[See Video to Reveal this Text or Code Snippet]]

Key Components of the Query:

SELECT DISTINCT: This ensures we get unique day entries in our output.

percentile_cont(price, 0.5): This part calculates the median price for all products sold on specific days.

Window Function - OVER (PARTITION BY day): This clause groups the data by day, enabling us to calculate the median specifically for each day without mixing it with other days' data.

CASE WHEN Clause: This clause filters the prices specifically for apples. We use it to ensure we only evaluate prices for apples when computing the median for that category.

IGNORE NULLS: This option tells the function to ignore any NULL values in its calculation, ensuring a more accurate median, particularly when filtering for apples.

Final Thoughts

By implementing this query, you can easily derive both the overall and specific median prices for your dataset. BigQuery’s percentile_cont() function makes this task straightforward, enabling you to focus on analyzing the important metrics for your business!

If you have any further questions or need assistance with other SQL issues, feel free to reach out in the comments below!