دوال الأرقام Numeric Functions

-- الجمع
SELECT 10 + 5;           -- 15

-- الطرح
SELECT 10 - 5;           -- 5

-- الضرب
SELECT 10 * 5;           -- 50

-- القسمة
SELECT 10 / 4;           -- 2.5 (أو 2 في بعض القواعد)
SELECT 10.0 / 4;         -- 2.5 (ضمان النتيجة العشرية)

-- باقي القسمة (Modulo)
SELECT 10 % 3;           -- 1
SELECT MOD(10, 3);       -- 1

-- الأس
SELECT POWER(2, 10);     -- 1024
SELECT POW(2, 10);       -- 1024 (MySQL)

-- الجذر التربيعي
SELECT SQRT(144);        -- 12

-- حساب إجمالي الطلب
SELECT
    product_name,
    quantity,
    unit_price,
    quantity * unit_price AS total_price
FROM order_items;

-- حساب السعر بعد الخصم
SELECT
    product_name,
    price,
    discount_percent,
    price * (1 - discount_percent / 100) AS final_price
FROM products;

-- حساب نسبة الربح
SELECT
    product_name,
    cost_price,
    sell_price,
    sell_price - cost_price AS profit,
    ((sell_price - cost_price) / cost_price) * 100 AS profit_percentage
FROM products;

-- ROUND: تقريب لأقرب قيمة
SELECT ROUND(3.14159);        -- 3
SELECT ROUND(3.14159, 2);     -- 3.14
SELECT ROUND(3.14159, 4);     -- 3.1416
SELECT ROUND(1234.56, -2);    -- 1200 (تقريب لأقرب مئة)

-- FLOOR: تقريب للأسفل (أكبر عدد صحيح أصغر أو يساوي)
SELECT FLOOR(3.9);            -- 3
SELECT FLOOR(-3.1);           -- -4

-- CEIL/CEILING: تقريب للأعلى (أصغر عدد صحيح أكبر أو يساوي)
SELECT CEIL(3.1);             -- 4
SELECT CEILING(3.1);          -- 4
SELECT CEIL(-3.9);            -- -3

-- TRUNCATE: قطع بدون تقريب
SELECT TRUNCATE(3.14159, 2);  -- 3.14 (MySQL)
SELECT TRUNC(3.14159, 2);     -- 3.14 (Oracle, PostgreSQL)

-- تقريب الأسعار لأقرب ريال
SELECT
    product_name,
    price,
    ROUND(price) AS rounded_price
FROM products;

-- حساب عدد الصفحات المطلوبة (10 عناصر لكل صفحة)
SELECT
    COUNT(*) AS total_items,
    CEIL(COUNT(*) / 10.0) AS total_pages
FROM products;

-- تقريب النسب المئوية
SELECT
    category,
    COUNT(*) AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS percentage
FROM products
GROUP BY category;

-- ABS: القيمة المطلقة
SELECT ABS(-15);          -- 15
SELECT ABS(15);           -- 15
SELECT ABS(-3.14);        -- 3.14

-- SIGN: إشارة الرقم (-1, 0, 1)
SELECT SIGN(-15);         -- -1
SELECT SIGN(0);           -- 0
SELECT SIGN(15);          -- 1

-- حساب الفرق المطلق بين تاريخين
SELECT
    order_date,
    ship_date,
    ABS(DATEDIFF(ship_date, order_date)) AS days_diff
FROM orders;

-- تحديد نوع المعاملة (دخل/مصروف)
SELECT
    amount,
    CASE SIGN(amount)
        WHEN 1 THEN 'دخل'
        WHEN -1 THEN 'مصروف'
        ELSE 'صفر'
    END AS transaction_type
FROM transactions;

-- LOG: اللوغاريتم الطبيعي
SELECT LOG(2.718281828);     -- ≈ 1
SELECT LN(2.718281828);      -- ≈ 1 (PostgreSQL, Oracle)

-- LOG10: لوغاريتم أساس 10
SELECT LOG10(1000);          -- 3

-- LOG2: لوغاريتم أساس 2
SELECT LOG2(1024);           -- 10

-- EXP: الأس (e^x)
SELECT EXP(1);               -- ≈ 2.718281828

-- PI: قيمة π
SELECT PI();                 -- 3.141592653589793

-- الدوال المثلثية (بالراديان)
SELECT SIN(PI()/2);          -- 1
SELECT COS(0);               -- 1
SELECT TAN(PI()/4);          -- ≈ 1

-- تحويل الدرجات للراديان
SELECT RADIANS(180);         -- ≈ 3.14159
SELECT DEGREES(PI());        -- 180

-- حساب المسافة بين موقعين جغرافيين بالكيلومترات
SELECT
    s1.store_name,
    s2.store_name,
    6371 * ACOS(
        COS(RADIANS(s1.latitude)) *
        COS(RADIANS(s2.latitude)) *
        COS(RADIANS(s2.longitude) - RADIANS(s1.longitude)) +
        SIN(RADIANS(s1.latitude)) *
        SIN(RADIANS(s2.latitude))
    ) AS distance_km
FROM stores s1
CROSS JOIN stores s2
WHERE s1.store_id < s2.store_id;

-- MySQL: RAND() يُرجع رقم بين 0 و 1
SELECT RAND();               -- مثال: 0.7432...

-- رقم عشوائي بين 1 و 100
SELECT FLOOR(RAND() * 100) + 1;

-- رقم عشوائي بين min و max
SELECT FLOOR(RAND() * (max - min + 1)) + min;

-- PostgreSQL: RANDOM()
SELECT RANDOM();

-- SQL Server: RAND() أو NEWID()
SELECT RAND();

-- اختيار سجلات عشوائية
SELECT *
FROM products
ORDER BY RAND()
LIMIT 5;

-- اختيار فائز عشوائي
SELECT customer_name
FROM customers
ORDER BY RAND()
LIMIT 1;

-- MySQL: FORMAT(number, decimals)
SELECT FORMAT(1234567.891, 2);    -- '1,234,567.89'

-- تنسيق الأسعار
SELECT
    product_name,
    FORMAT(price, 2) AS formatted_price,
    CONCAT('$', FORMAT(price, 2)) AS price_with_symbol
FROM products;

-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(1234567.89, '9,999,999.99');  -- ' 1,234,567.89'
SELECT TO_CHAR(1234567.89, 'FM9,999,999.99'); -- '1,234,567.89'

-- SQL Server: FORMAT
SELECT FORMAT(1234567.89, 'N2');  -- '1,234,567.89'
SELECT FORMAT(1234567.89, 'C');   -- '$1,234,567.89'

-- Oracle: TO_CHAR
SELECT TO_CHAR(1234567.89, '9,999,999.99') FROM dual;

-- MySQL: تنسيق كامل
SELECT
    product_name,
    CONCAT(FORMAT(price, 2), ' ر.س') AS price_sar,
    CONCAT(FORMAT(price * 0.15, 2), ' ر.س') AS vat,
    CONCAT(FORMAT(price * 1.15, 2), ' ر.س') AS total_with_vat
FROM products;

-- تنسيق النسب المئوية
SELECT
    category,
    COUNT(*) AS count,
    CONCAT(
        FORMAT(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1),
        '%'
    ) AS percentage
FROM products
GROUP BY category;

SELECT
    invoice_id,
    customer_name,
    subtotal,
    ROUND(subtotal * 0.15, 2) AS vat,
    ROUND(subtotal * 1.15, 2) AS total,
    CASE
        WHEN subtotal >= 1000 THEN ROUND(subtotal * 0.05, 2)
        ELSE 0
    END AS discount,
    ROUND(
        subtotal * 1.15 -
        CASE WHEN subtotal >= 1000 THEN subtotal * 0.05 ELSE 0 END,
        2
    ) AS final_total
FROM invoices;

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS total_orders,
    ROUND(SUM(total_amount), 2) AS total_sales,
    ROUND(AVG(total_amount), 2) AS avg_order_value,
    ROUND(MIN(total_amount), 2) AS min_order,
    ROUND(MAX(total_amount), 2) AS max_order,
    ROUND(
        SUM(total_amount) * 100 /
        SUM(SUM(total_amount)) OVER(),
        1
    ) AS sales_percentage
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        SUM(total_amount) AS sales
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    month,
    sales,
    LAG(sales) OVER (ORDER BY month) AS prev_month_sales,
    ROUND(
        (sales - LAG(sales) OVER (ORDER BY month)) /
        LAG(sales) OVER (ORDER BY month) * 100,
        2
    ) AS growth_rate
FROM monthly_sales;

SELECT
    sale_date,
    daily_sales,
    ROUND(
        AVG(daily_sales) OVER (
            ORDER BY sale_date
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7_days
FROM daily_sales_summary
ORDER BY sale_date;

SELECT
    customer_id,
    customer_name,
    total_purchases,
    CASE
        WHEN total_purchases >= 10000 THEN 'VIP'
        WHEN total_purchases >= 5000 THEN 'ذهبي'
        WHEN total_purchases >= 1000 THEN 'فضي'
        ELSE 'عادي'
    END AS customer_tier,
    NTILE(4) OVER (ORDER BY total_purchases DESC) AS quartile
FROM (
    SELECT
        c.customer_id,
        c.customer_name,
        COALESCE(SUM(o.total_amount), 0) AS total_purchases
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
) customer_totals;

-- ❌ سيء: حساب في WHERE يمنع استخدام الفهرس
SELECT * FROM products WHERE price * 1.15 > 100;

-- ✅ جيد: الحساب على الثابت
SELECT * FROM products WHERE price > 100 / 1.15;

-- ❌ سيء: تكرار نفس الحساب
SELECT
    quantity * price AS subtotal,
    quantity * price * 0.15 AS vat,
    quantity * price * 1.15 AS total
FROM order_items;

-- ✅ جيد: حساب مرة واحدة
SELECT
    subtotal,
    subtotal * 0.15 AS vat,
    subtotal * 1.15 AS total
FROM (
    SELECT quantity * price AS subtotal
    FROM order_items
) calculated;