google-big-queryHow can I use Google BigQuery to create a pivot table?
Google BigQuery is a powerful data warehouse tool that can be used to create a pivot table. A pivot table is a type of summary table that allows users to quickly summarize large datasets.
To create a pivot table in BigQuery, you must first create a query that contains the data you want to summarize. For example, the following query will return the total number of orders for each product in the orders table:
SELECT product, COUNT(*) AS num_orders
FROM orders
GROUP BY product
Output example
product num_orders
Product A 100
Product B 200
Product C 300
Once the query is written, you can use the pivot
clause to create the pivot table. The following example will create a pivot table that displays the total number of orders for each product by month:
SELECT *
FROM (
SELECT product, EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS num_orders
FROM orders
GROUP BY product, month
)
PIVOT (
SUM(num_orders)
FOR month IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
)
Output example
product 1 2 3 4 5 6 7 8 9 10 11 12
Product A 10 20 30 40 50 60 70 80 90 100 110 120
Product B 20 40 60 80 100 120 140 160 180 200 220 240
Product C 30 60 90 120 150 180 210 240 270 300 330 360
The code above consists of two parts:
-
The inner query
SELECT product, EXTRACT(MONTH FROM order_date) AS month, COUNT(*) AS num_orders FROM orders GROUP BY product, month
which returns the total number of orders for each product by month. -
The
PIVOT
clause which creates the pivot table from the query results, using theSUM
aggregation function to calculate the total number of orders for each product by month.
For more information about creating pivot tables in BigQuery, please refer to the BigQuery documentation.
More of Google Big Query
- ¿Cuáles son las ventajas y desventajas de usar Google BigQuery?
- How can I use Google Big Query to count the number of zeros in a given dataset?
- How do I use Google Big Query with Zoom?
- How do I use Google Big Query to zip files?
- How do I set up a Google Big Query zone?
- How can I use Google Big Query to integrate with Zephyr?
- How do I use the YEAR function in Google BigQuery?
- How do I query Google BigQuery using XML?
- How can I use Google BigQuery to retrieve data from a specific year?
- How can I export data from Google Big Query to an XLSX file?
See more codes...