-- 安装并加载 DuckLake 扩展
INSTALL ducklake;
LOAD ducklake;

-- 创建 DuckLake 表并启用数据内联
CREATE OR REPLACE TABLE sensor_readings (
    ts TIMESTAMP,
    sensor_id INTEGER,
    temperature DOUBLE,
    humidity DOUBLE
) USING ducklake
LOCATION 's3://my-bucket/sensor-data/'
WITH (
    data_inlining = true,
    inline_size_limit = '1MB'
);

-- 写入数据（小批量写入将被内联到清单中）
INSERT INTO sensor_readings VALUES
    ('2026-05-15 10:00:00', 1, 22.5, 65.0),
    ('2026-05-15 10:00:01', 2, 23.1, 63.5),
    ('2026-05-15 10:00:02', 3, 21.8, 67.2);

-- 读取数据
SELECT sensor_id, avg(temperature) AS avg_temp
FROM sensor_readings
WHERE ts >= '2026-05-15 00:00:00'
GROUP BY sensor_id
ORDER BY sensor_id;

-- 创建排序表 + 桶分区
CREATE TABLE orders (
    order_id BIGINT,
    customer_id INTEGER,
    order_date DATE,
    amount DECIMAL(10,2)
) USING ducklake
LOCATION 's3://my-bucket/orders/'
WITH (
    sort_by = 'order_date',
    bucket_partitions = 16,
    bucket_column = 'customer_id'
);

-- 写入 100 万条示例数据
INSERT INTO orders
SELECT
    range AS order_id,
    (range % 10000)::INTEGER AS customer_id,
    '2026-01-01'::DATE + (range % 365) AS order_date,
    (random() * 1000)::DECIMAL(10,2) AS amount
FROM range(1, 1000000);

-- 范围查询将受益于排序
SELECT customer_id, sum(amount) AS total_spent
FROM orders
WHERE order_date BETWEEN '2026-06-01' AND '2026-06-30'
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10;

INSTALL iceberg;
LOAD iceberg;
INSTALL spatial;
LOAD spatial;

-- 查询包含 GEOMETRY 列的 Iceberg 表
SELECT 
    st_area(geometry) AS area,
    count(*) AS num_parcels
FROM 's3://my-bucket/land-parcels.iceberg'
WHERE st_within(
    geometry,
    st_geomfromtext('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')
)
GROUP BY st_area(geometry)
ORDER BY area DESC
LIMIT 5;

-- 创建 Iceberg 分区表
CREATE TABLE metrics_iceberg AS
SELECT * FROM read_parquet('metrics.parquet');

-- 将数据写入 Iceberg 格式（分区表）
COPY (
    SELECT * FROM metrics_iceberg
) TO 's3://my-bucket/metrics.iceberg'
(FORMAT ICEBERG, PARTITION_BY (event_date));

-- 更新和删除操作现在支持分区表
UPDATE 's3://my-bucket/metrics.iceberg'
SET status = 'archived'
WHERE event_date < '2025-01-01';

DELETE FROM 's3://my-bucket/metrics.iceberg'
WHERE event_date < '2024-01-01';

-- 使用 truncate 分区（按字符串前缀）
CREATE TABLE user_events_iceberg
AS SELECT * FROM read_parquet('events.parquet');
COPY user_events_iceberg
TO 's3://my-bucket/events.iceberg'
(FORMAT ICEBERG, 
 PARTITION_BY (truncate(2, country_code)));

-- 使用 bucket 分区（按哈希）
COPY user_events_iceberg
TO 's3://my-bucket/events-bucketed.iceberg'
(FORMAT ICEBERG, 
 PARTITION_BY (bucket(16, user_id)));

-- 复现被 Jepsen 发现的 Bug（已在 1.5.2 中修复）
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    email VARCHAR
);

-- 插入初始数据
INSERT INTO users VALUES (1, 'Alice', '[email protected]');

-- 带冲突解决的 INSERT（曾触发错误）
INSERT INTO users VALUES (1, 'Alice Updated', '[email protected]')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name,
    email = EXCLUDED.email;

-- 1.5.2 中正常工作
SELECT * FROM users;
-- ┌─────┬───────────────┬────────────────────────┐
-- │ id  │     name      │         email          │
-- ├─────┼───────────────┼────────────────────────┤
-- │  1  │ Alice Updated │ [email protected]  │
-- └─────┴───────────────┴────────────────────────┘

-- 列出当前会话中的文件
.files

-- 从 URL 导入文件到浏览器
.files import https://datasets.duckdb.org/weather.parquet

-- 创建新文件
COPY (
    SELECT 'Hello, DuckDB!' AS greeting
) TO '/my-notes.txt';

-- 下载结果文件
.files download my-query-results.csv

-- 查询内置数据集
SELECT table_name, count(*) AS row_count
FROM information_schema.tables
WHERE table_schema = 'main'
GROUP BY table_name;

-- 安装 TPC-H 扩展
INSTALL tpch;
LOAD tpch;

-- 生成 SF10 测试数据
CALL dbgen(sf = 10);

-- 运行查询 6（报告型聚合查询）
EXPLAIN ANALYZE
SELECT
    sum(extendedprice * discount) AS revenue
FROM
    lineitem
WHERE
    shipdate >= '1994-01-01'
    AND shipdate < date '1994-01-01' + interval '1' year
    AND discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01
    AND quantity < 24;