MSSQL 기반 애플리케이션의 ClickHouse 전환

Driver={ODBC Driver 17 for SQL Server};Server=myserver;Database=mydb;Uid=user;Pwd=password;

Driver={ClickHouse ODBC Driver};Host=myserver;Port=8123;Database=mydb;Uid=user;Pwd=password;

jdbc:clickhouse://myserver:8123/mydb?user=user&password=password

SELECT TOP 100 * FROM sales ORDER BY amount DESC

SELECT * FROM sales ORDER BY amount DESC LIMIT 100

SELECT * FROM sales ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

SELECT * FROM sales ORDER BY id LIMIT 10 OFFSET 20

SELECT category, STRING_AGG(product_name, ', ') AS products
FROM products GROUP BY category

SELECT category, arrayStringConcat(groupArray(product_name), ', ') AS products
FROM products GROUP BY category

SELECT DATEADD(day, 7, order_date) FROM orders
SELECT DATEADD(month, -1, GETDATE())

SELECT order_date + INTERVAL 7 DAY FROM orders
SELECT now() - INTERVAL 1 MONTH

SELECT DATEDIFF(day, start_date, end_date) FROM projects

SELECT date_diff('day', start_date, end_date) FROM projects

SELECT EOMONTH(order_date)

SELECT toLastDayOfMonth(order_date)

SELECT DATETRUNC(month, order_date)

SELECT toStartOfMonth(order_date)
SELECT toStartOfYear(order_date)
SELECT toStartOfWeek(order_date)
SELECT toStartOfDay(order_date)
SELECT toStartOfHour(order_date)

SELECT
    CASE
        WHEN amount > 1000 THEN 'High'
        WHEN amount > 100 THEN 'Medium'
        ELSE 'Low'
    END AS tier
FROM sales

SELECT IIF(quantity > 0, 'In Stock', 'Out of Stock') FROM products

SELECT if(quantity > 0, 'In Stock', 'Out of Stock') FROM products

SELECT multiIf(
    amount > 1000, 'High',
    amount > 100, 'Medium',
    'Low'
) AS tier FROM sales

SELECT
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    SUM(CASE WHEN region = 'KR' THEN amount ELSE 0 END) AS kr_amount
FROM users

SELECT
    countIf(status = 'active') AS active_count,
    sumIf(amount, region = 'KR') AS kr_amount
FROM users

SELECT
    user_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM orders

WITH monthly_sales AS (
    SELECT
        toStartOfMonth(order_date) AS month,
        sum(amount) AS total
    FROM orders
    GROUP BY month
)
SELECT * FROM monthly_sales WHERE total > 10000

SELECT * FROM (
    SELECT year, quarter, revenue FROM sales
) src
PIVOT (
    SUM(revenue) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) pvt

SELECT
    year,
    sumIf(revenue, quarter = 'Q1') AS Q1,
    sumIf(revenue, quarter = 'Q2') AS Q2,
    sumIf(revenue, quarter = 'Q3') AS Q3,
    sumIf(revenue, quarter = 'Q4') AS Q4
FROM sales
GROUP BY year

SELECT CAST(column AS Int32) FROM table

SELECT TRY_CAST(value AS INT) FROM data

SELECT toInt32OrNull(value) FROM data    -- 실패 시 NULL
SELECT toInt32OrZero(value) FROM data    -- 실패 시 0

SELECT * INTO #temp_sales FROM sales WHERE year = 2024

CREATE TEMPORARY TABLE temp_sales AS
SELECT * FROM sales WHERE year = 2024

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
)