BigQuery のユーザー フレンドリーな SQL とともに快適な夏を

  -- we start with SQL to create a simple table
-- we only include gender, year, and number. 
 
CREATE TABLE
  mydataset.sampletable1 AS (
  SELECT
    Gender,Year,SUM(Number) AS Number
  FROM
    `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    Year >= 2017
  GROUP BY
    Gender, Year);
 
 
-- The resulting table:
--+----------------------------------------+
--|   Gender   |    Year    |    Number    |
--+----------------------------------------+
--|      F     |    2019    |    1353716   |
--|      F     |    2017    |    1403989   |
--|      F     |    2018    |    1380382   |
--|      M     |    2018    |    1568678   |
--|      M     |    2019    |    1538056   |
--|      M     |    2017    |    1604609   |
--+----------------------------------------+
 
-- use PIVOT to create columns for “female” and “male” 
 
CREATE TABLE
  mydataset.Pivoted AS
SELECT
  year, male, female
FROM
  mydataset.sampletable1 
PIVOT( SUM(Number) FOR gender IN ('M' AS male,
  'F' AS female))
ORDER BY
  year;
 
-- The resulting pivoted table:
--+----------------------------------------+
--|    Year    |   female   |     male     |
--+----------------------------------------+
--|    2017    |   1403989   |    1604609  |
--|    2018    |   1380382   |    1568678  |
--|    2019    |   1353716   |    1538056  |
--+----------------------------------------+

-- UNPIVOT reverses the row/column rotation of PIVOT.

SELECT
  *
FROM
  mydataset.Pivoted 
UNPIVOT(number FOR gender IN (male AS 'M',
  female AS 'F'));

  -- QUALIFY filters the result of the RANK function
 
SELECT
  name,year,SUM(number) AS total,
  RANK() OVER (PARTITION BY year 
  ORDER BY SUM(number) DESC) AS rank
FROM
  `bigquery-public-data.usa_names.usa_1910_current`
WHERE
  gender = 'F'
  AND YEAR >= 2010
GROUP BY 1,2 
QUALIFY RANK <= 3
ORDER BY 2,4;

  -- create a sample table “tablename” in “mydataset”. 
-- You will rename this table.

CREATE OR REPLACE TABLE dataset.tablename(
    col1 STRING, 
    col2 NUMERIC);

-- if this table “tablename” becomes obsolete
-- perform Table Rename to “obsoletetable”

ALTER TABLE
  mydataset.name RENAME TO obsoletetable;

  -- create a table to store credit card numbers
-- the business requires this field, so 
-- include a NOT NULL constraint 
CREATE TABLE
  mydataset.customers(credit_card_number STRING NOT NULL);

-- if needs of the business no longer require this field,
-- the customer can allow null entries in this column
-- by dropping the constraint

ALTER TABLE
  mydataset.customers 
ALTER COLUMN credit_card_number DROP NOT NULL;

  -- aliases list1 and list2 can be assigned in a list format

CREATE VIEW
  myview (list1, list2) AS
SELECT
  column_1, column_2
FROM
  mydataset.exampletable

  -- pull the first, second, penultimate and final points 
-- from a linestring

WITH linestring as (
    SELECT ST_GeogFromText('linestring(1 1, 2 1, 3 2, 3 3)') g
)
SELECT
  ST_StartPoint(g) AS first, ST_EndPoint(g) AS last,
  ST_PointN(g,2) AS second, ST_PointN(g, -2) as second_to_last
FROM
  linestring

+--------------+--------------+--------------+----------------+
| first        | last         | second       | second_to_last |
+--------------+--------------+--------------+----------------+
| POINT(1 1)   | POINT(3 3)   | POINT(2 1)   | POINT(3 2)     |
+--------------+--------------+--------------+----------------+