DuckDB

import duckdb
import pandas as pd

# 1. 인메모리 데이터베이스 생성 (기본)
con = duckdb.connect()

# 2. CSV 파일 직접 쿼리 (로드 없이)
result = con.execute("""
    SELECT
        category,
        COUNT(*) as count,
        AVG(price) as avg_price,
        SUM(quantity) as total_qty
    FROM read_csv_auto('sales_data.csv')
    GROUP BY category
    ORDER BY total_qty DESC
    LIMIT 10
""").fetchdf()

print(result)

# 3. Parquet 파일 쿼리 (S3 직접 접근)
con.execute("INSTALL httpfs; LOAD httpfs;")
con.execute("SET s3_region='ap-northeast-2';")

remote_result = con.execute("""
    SELECT * FROM read_parquet('s3://my-bucket/data/*.parquet')
    WHERE event_date >= '2024-01-01'
""").fetchdf()

# 4. Pandas DataFrame과 통합
df = pd.DataFrame({
    'user_id': ['u1', 'u2', 'u3', 'u1', 'u2'],
    'product': ['A', 'B', 'A', 'C', 'A'],
    'amount': [100, 200, 150, 300, 250]
})

# DataFrame을 DuckDB에서 직접 쿼리
user_summary = con.execute("""
    SELECT
        user_id,
        COUNT(DISTINCT product) as product_count,
        SUM(amount) as total_amount
    FROM df
    GROUP BY user_id
""").fetchdf()

# 5. 영구 데이터베이스 파일 사용
con_persistent = duckdb.connect('analytics.duckdb')

con_persistent.execute("""
    CREATE TABLE IF NOT EXISTS events (
        event_id INTEGER,
        event_type VARCHAR,
        user_id VARCHAR,
        timestamp TIMESTAMP
    )
""")

# 6. 대용량 파일 효율적 처리 (파티션별 병렬 읽기)
con.execute("""
    COPY (
        SELECT * FROM read_parquet('data/year=*/month=*/*.parquet',
                                   hive_partitioning=true)
        WHERE year = 2024
    ) TO 'output.parquet' (FORMAT PARQUET)
""")

# 7. Window 함수 활용
con.execute("""
    SELECT
        user_id,
        event_date,
        daily_amount,
        SUM(daily_amount) OVER (
            PARTITION BY user_id
            ORDER BY event_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) as cumulative_amount
    FROM daily_summary
""")

print("DuckDB 분석 완료!")
con.close()