CREATE TABLE weather (
    city    VARCHAR,
    temp_lo INTEGER, -- minimum temperature on a day
    temp_hi INTEGER, -- maximum temperature on a day
    prcp    FLOAT,
    date    DATE
);
从csv文件导入数据并建表
CREATE TABLE 'cars' AS SELECT * FROM read_csv_auto('cars.csv');
CREATE TEMP TABLE t1 AS SELECT * FROM read_csv('path/file.csv');
COPY lineitem FROM 'lineitem.csv';

CREATE TABLE Cities (
    Country VARCHAR, Name VARCHAR, Year INTEGER, Population INTEGER
);
INSERT INTO Cities VALUES
    ('NL', 'Amsterdam', 2000, 1005),
    ('NL', 'Amsterdam', 2010, 1065),
    ('NL', 'Amsterdam', 2020, 1158),
    ('US', 'Seattle', 2000, 564),
    ('US', 'Seattle', 2010, 608),
    ('US', 'Seattle', 2020, 738),
    ('US', 'New York City', 2000, 8015),
    ('US', 'New York City', 2010, 8175),
    ('US', 'New York City', 2020, 8772);

PIVOT Cities
ON Year
USING sum(Population);

PIVOT Cities
ON Year
USING sum(Population)
GROUP BY Country;

PIVOT Cities
ON Year IN (2000, 2010)
USING sum(Population)
GROUP BY Country;

PIVOT Cities
ON Country, Name
USING sum(Population);

PIVOT Cities
ON Year
USING sum(Population) AS total, max(Population) AS max
GROUP BY Country;

PIVOT Cities
ON Year
USING sum(Population)
GROUP BY Country, Name;