Skip to content

Instantly share code, notes, and snippets.

View FurcyPin's full-sized avatar

FurcyPin

10 followers · 0 following
View GitHub Profile
@FurcyPin
FurcyPin / price_with_promotion_history.sql
Created March 1, 2024 10:25
WITH price_history_per_shop_per_article AS (
SELECT
shop_id,
article_id,
ARRAY_AGG((SELECT AS STRUCT T.* EXCEPT(shop_id, article_id)) ORDER BY start_datetime) as price_history
FROM `supermarket.sell_price_history` T
GROUP BY shop_id, article_id
)
, promotion_history_per_shop_per_article AS (
SELECT
@FurcyPin
FurcyPin / bigquery-frame_analyze_example-full.py
Last active February 20, 2022 10:49
from bigquery_frame import BigQueryBuilder
from bigquery_frame.auth import get_bq_client
from bigquery_frame.transformations import analyze
from bigquery_frame import functions as f
bigquery = BigQueryBuilder(get_bq_client())
query = """
SELECT
@FurcyPin
FurcyPin / bigquery-frame_analyze_example-part2.py
Last active February 20, 2022 10:39
df = bigquery.sql(query)
df.show()
# +----+------------+---------------------+--------------------------------------------+
# | id | name | types | evolution |
# +----+------------+---------------------+--------------------------------------------+
# | 1 | Bulbasaur | ['Grass', 'Poison'] | {'can_evolve': True, 'evolves_from': None} |
# | 2 | Ivysaur | ['Grass', 'Poison'] | {'can_evolve': True, 'evolves_from': 1} |
# | 3 | Venusaur | ['Grass', 'Poison'] | {'can_evolve': False, 'evolves_from': 2} |
# | 4 | Charmander | ['Fire'] | {'can_evolve': True, 'evolves_from': None} |
@FurcyPin
FurcyPin / bigquery-frame_flatten_example.py
Created February 13, 2022 15:27
bq = BigQueryBuilder(get_bq_client())
df = bq.sql('''SELECT 1 as id, STRUCT(1 as a, STRUCT(1 as c, 1 as d) as b) as s''')
df.printSchema()
# root
# |-- id: INTEGER (NULLABLE)
# |-- s: RECORD (NULLABLE)
# | |-- a: INTEGER (NULLABLE)
# | |-- b: RECORD (NULLABLE)
# | | |-- c: INTEGER (NULLABLE)
# | | |-- d: INTEGER (NULLABLE)
@FurcyPin
FurcyPin / blog-sql-is-not-enough-sort_columns.sql
Last active January 30, 2022 09:07
WITH T AS (
/* SOME TRANSFORMATION */
)
SELECT
-- Please keep these column names sorted alphabetically
col1,
col2,
col3,
col4,
...
@FurcyPin
FurcyPin / bigquery-frame_bigquery_example.py
Last active January 23, 2022 11:47
from bigquery_frame import BigQueryBuilder
from bigquery_frame.auth import get_bq_client
from bigquery_frame import functions as f
bigquery = BigQueryBuilder(get_bq_client())
df = bigquery.sql("""
SELECT 1 as id, "Bulbasaur" as name, ["Grass", "Poison"] as types, NULL as other_col
UNION ALL
@FurcyPin
FurcyPin / bigquery-frame_spark_example.py
Created January 23, 2022 11:46
from pyspark.sql import SparkSession
from pyspark.sql import functions as f
spark = SparkSession.builder.master("local[1]").getOrCreate()
df = spark.sql("""
SELECT 1 as id, "Bulbasaur" as name, ARRAY("Grass", "Poison") as types, NULL as other_col
UNION ALL
SELECT 2 as id, "Ivysaur" as name, ARRAY("Grass", "Poison") as types, NULL as other_col
""")