I have two tables, one containing orders with a nested line_items structure and another with a pricing history for each product sku code. Orders Table order_id order_date item_sku item_quantity item_subtotal 1 2022-23-07 SKU1 7 12.34 SKU2 1 9.99 2 2022-12-07 SKU1 1 1.12 SKU3 5 32.54 Price History Table item_s…
Tag: google-cloud-platform
CURRENT in BigQuery?
I’ve noticed that CURRENT is a reserved keyword for BigQuery at: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical. What exactly does CURRENT do? I’ve only seen it as a prefix for things such as CURRENT_TIME(), CURRENT_DATE(), and other such stuff but have never seen it by itse…
Compare two columns in SQL
I’m new to SQL and have very basic queries in GCP. Let’s consider this table below: Name B C Arun 1234-5678 1234 Tara 6789 – 7654 6789 Arun 4567 4324 Here, I want to compare column B and C and if they match then give 1 else 0 in column same and else different (which we have to create). So
ROW type/constructor in BigQuery
Does BigQuery have the concept of a ROW, for example, similar to MySQL or Postgres or Oracle or Snowflake? I know it sort of implicitly uses it when doing an INSERT … VALUES (…) , for example: Each of the values would be implicitly be a ROW type of the Inventory table, but is this construction all…
get rows that have the same value column
I’m trying to select rows that have the same column values using BigQuery on Githubs public data. I’d approach it like so using SQL server but I am getting “Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into…
Unnest Query optimisation for singular record
I’m trying to optimise my query for when an internal customer only want to return one result *(and it’s associated nested dataset). My aim is to reduce the query process size. However, it appears to be the exact same value regardless of whether I’m querying for 1 record (with unnested 48,000…
How to find the index of an element in an Array in BigQuery
is it possible to find the index of an element in a array in BigQuery? The idea is to find the index of an element to grab a different element in the row below. So for example, for this case I need to find the index of ‘status2’ to grab the respective entering date of the status after status2, tha…
How to remove duplicated rows from table with arrays in BigQuery
there is a table in BigQuery that has REPEATED type columns and has duplicated rows, since the table has arrays I cannot use distinct to grab only one row. Table looks something like this: I want to remove the duplicated rows, the output should be like this: I didn’t find a way to come up with the above…
How to change arrays into rows
There is a table in BigQuery that contains 2 REPEATED(arrays) type of columns, enterded_date and status. Table in BigQuery: Is it possible to make a query that returns rows instead? Like this: Answer Consider below approach if applied to sample data in your question – output is
Is there a way/service to get SQL statements that would input all the values of a database into another when inputting a database? [closed]
Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers. Want to improve this question? Update the question so it’s on-topic for Stack Overflow. Closed last month. Improve this question I have a local SQL database that I want to move onto google cloud’s…