# 公式サイトから実行ファイルをダウンロード
wget https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
./duckdb test.db

pip install duckdb

import duckdb
conn = duckdb.connect()

install.packages("duckdb")
library(duckdb)
con <- dbConnect(duckdb::duckdb())

-- CSV直接読み込み
SELECT * FROM 'sales_data.csv';

-- Parquet形式
SELECT customer_id, SUM(amount) 
FROM 'transactions.parquet' 
GROUP BY customer_id;

-- JSON形式
SELECT json_extract(data, '$.name') as name 
FROM 'users.json';

-- 実行時間の測定
.timer on
SELECT region, COUNT(*), AVG(sales) 
FROM 'large_dataset.parquet' 
GROUP BY region;
-- 実行時間: 2.3秒（10GB Parquetファイル）

-- 自動スキーマ検出付きで大容量CSVを高速読み込み
SELECT * FROM read_csv_auto('large_dataset.csv');

-- 顧客別売上ランキング生成
SELECT customer_id, amount,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) as rank
FROM sales_data;

-- 日別売上集計ビュー
CREATE VIEW daily_stats AS 
SELECT date, SUM(amount) FROM sales GROUP BY date;

-- 複数ファイルを一括処理
SELECT * FROM read_csv_auto('logs/*.csv');

-- パーティション別集計
SELECT year, month, COUNT(*) 
FROM events 
WHERE date > '2024-01-01'
GROUP BY year, month;

-- メモリ効率的なParquetエクスポート
COPY (SELECT * FROM large_table WHERE date > '2024-01-01') 
TO 'output.parquet';

-- S3バケットからParquetファイルを直接読み込み
SELECT * FROM 's3://my-bucket/sales-data.parquet'
WHERE date >= '2024-01-01';

-- WebサーバーのCSVファイルを分析
SELECT country, SUM(revenue) 
FROM 'https://example.com/data.csv' 
GROUP BY country;

-- 指定地点から半径1km以内の店舗を検索
SELECT store_name, address 
FROM stores 
WHERE ST_DWithin(location, ST_Point(139.7673, 35.6809), 1000);

-- 地理的エリアの面積計算
SELECT region, ST_Area(geometry) as area_sqm
FROM administrative_areas;

-- JSON配列から特定の値を抽出
SELECT JSON_EXTRACT(user_data, '$.profile.age') as age
FROM user_logs 
WHERE JSON_ARRAY_LENGTH(JSON_EXTRACT(user_data, '$.purchases')) > 5;

-- 複雑なJSONパスの一括処理
SELECT JSON_EXTRACT_PATH(data, 'user', 'preferences', 'categories')
FROM analytics_events;

-- 売上成長率計算マクロ
CREATE MACRO sales_growth(current_val, previous_val) AS 
((current_val - previous_val) / previous_val * 100);

-- カスタムPython関数でテキスト分析
CREATE FUNCTION sentiment_analysis(text VARCHAR) RETURNS FLOAT 
LANGUAGE python AS 'from textblob import TextBlob; return TextBlob(text).sentiment.polarity';

-- メモリ制限の設定
SET memory_limit = '8GB';

-- ストリーミング処理でメモリ効率を向上
SELECT * FROM read_csv_auto('huge_file.csv')
WHERE condition = 'target'
LIMIT 1000;

-- フィルタを早期適用でパフォーマンス向上
SELECT customer_id, SUM(amount)
FROM sales 
WHERE date >= '2024-01-01'  -- WHERE句を先に適用
GROUP BY customer_id;

-- インデックスの効果的な活用
CREATE INDEX idx_date ON sales(date);

-- Parquet形式での保存
COPY sales TO 'optimized_sales.parquet' (FORMAT PARQUET);

-- CSVファイルの高速読み込み
COPY sales FROM 'sales_data.csv' (AUTO_DETECT TRUE);

-- Parquet形式での永続化
COPY sales TO 'sales_optimized.parquet' (FORMAT PARQUET);

-- 月次売上トレンドの算出
SELECT 
    date_trunc('month', order_date) as month,
    SUM(amount) as monthly_sales,
    LAG(SUM(amount), 1) OVER (ORDER BY date_trunc('month', order_date)) as prev_month,
    ROUND((SUM(amount) / LAG(SUM(amount), 1) OVER (ORDER BY date_trunc('month', order_date)) - 1) * 100, 2) as growth_rate
FROM sales 
GROUP BY date_trunc('month', order_date)
ORDER BY month;

-- RFM分析による顧客ランク付け
WITH rfm_base AS (
    SELECT 
        customer_id,
        MAX(order_date) as last_order_date,
        COUNT(*) as frequency,
        SUM(amount) as monetary
    FROM sales 
    GROUP BY customer_id
),
rfm_scores AS (
    SELECT *,
        NTILE(5) OVER (ORDER BY DATE_DIFF('day', last_order_date, CURRENT_DATE)) as R,
        NTILE(5) OVER (ORDER BY frequency DESC) as F,
        NTILE(5) OVER (ORDER BY monetary DESC) as M
    FROM rfm_base
)
SELECT 
    customer_id,
    CASE 
        WHEN R >= 4 AND F >= 4 AND M >= 4 THEN 'Champions'
        WHEN R >= 3 AND F >= 3 THEN 'Loyal Customers'
        WHEN R <= 2 THEN 'At Risk'
        ELSE 'Potential Loyalists'
    END as customer_segment
FROM rfm_scores;

-- 日次ダッシュボード用の集計ビュー
CREATE VIEW daily_summary AS
SELECT 
    DATE(order_date) as sales_date,
    COUNT(*) as order_count,
    SUM(amount) as total_sales,
    AVG(amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM sales 
GROUP BY DATE(order_date);