کار با توابع مرتبط با تاریخ

در دنیای واقعی، داده‌ها اغلب با زمان مرتبط هستند: زمان سفارش، زمان ارسال، تولد یا استخدام کارمندان و غیره. برای تحلیل داده‌ها، باید بتوانیم بخش‌های تاریخ را استخراج کنیم، داده‌ها را بر اساس بازه‌های زمانی گروه‌بندی کنیم، تاخیرها و اختلافات زمانی را محاسبه کنیم و نتایج را در قالب گزارش‌های قابل فهم ارائه کنیم. در این بخش، با استفاده از دیتابیس Northwind، این مهارت‌ها را تمرین خواهیم کرد.

• استخراج بخش‌های مختلف یک تاریخ (سال، ماه، روز و …)
• گرد کردن تاریخ‌ها به واحدهای زمانی مشخص (DATE_TRUNC)
• انجام محاسبات روی تاریخ‌ها و اختلاف‌ها (Interval)
• ترکیب تحلیل‌های زمانی با گروه‌بندی و توابع تجمعی
• فرمت‌دهی نتایج برای ارائه گزارش‌های خوانا

Date Columns in Northwind

ستون‌های مهم حاوی داده‌های زمانی در Northwind:

این ستون‌ها معمولاً از نوع DATE یا TIMESTAMP هستند.

Extracting Components from Dates

برای استخراج بخشی از تاریخ، از تابع EXTRACT() استفاده می‌کنیم:

EXTRACT(YEAR FROM order_date)
EXTRACT(MONTH FROM order_date)
EXTRACT(DAY FROM order_date)
EXTRACT(DOW FROM order_date)  -- روز هفته (۰=یکشنبه … ۶=شنبه)

نمونه عملی:

SELECT 
    order_id,
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month
FROM orders
LIMIT 5;

تمرین:

• استخراج سال و ماه از shipped_date
• پیدا کردن تعداد سفارش‌ها در سال ۱۹۹۷

Truncating Dates for Grouping (DATE_TRUNC)

تابع DATE_TRUNC() تاریخ یا زمان را به نزدیک‌ترین واحد مشخص گرد می‌کند، که برای گروه‌بندی بسیار مناسب است. مثلا کوئری زیر تمام مقادیر order_date را به روز اول همان ماه تبدیل می‌کند و این باعث می شود که بتوانیم به ازای ماه‌های مختلف از سال‌های مختلف گروه بندی و گزارش گیری داشته باشیم .

SELECT
    DATE_TRUNC('month', order_date) AS month_start,
    COUNT(*) AS total_orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;

نمونه برای خلاصه‌سازی فصلی:

SELECT
    DATE_TRUNC('quarter', order_date) AS quarter_start,
    SUM(freight) AS total_freight
FROM orders
GROUP BY DATE_TRUNC('quarter', order_date)
ORDER BY quarter_start;

Filtering by Dates and Ranges

با BETWEEN یا EXTRACT می‌توانیم بازه‌های زمانی را فیلتر کنیم:

SELECT *
FROM orders
WHERE order_date BETWEEN '1997-01-01' AND '1997-12-31';

SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 1997;

تمرین:

• پیدا کردن سفارش‌های ارسال شده در دسامبر
• پیدا کردن سفارش‌های Q2 سال ۱۹۹۸

Calculating Durations and Delays

می‌توان اختلاف دو تاریخ را مستقیماً محاسبه کرد:

SELECT
    order_id,
    shipped_date - order_date AS processing_days
FROM orders
WHERE shipped_date IS NOT NULL;

Interval چیست؟

INTERVAL یک نوع داده در PostgreSQL است که بازه زمانی را نشان می‌دهد. هنگامی که دو تاریخ را از هم تفریق میکنیم نتیجه یک بازه زمانی است و زمانی که می خواهیم مثلا بگوییم تمام داده های یک هفته اخیر ، زمان الان را منهای بازه یک هفته اخیر میکنیم. بنابراین یک نوع داده بسیار کاربردی است و به کمک آن می‌توان افزودن یا تفریق زمان‌ها را مشابه زیر انجام داد:

-- سفارش‌هایی که بیش از یک هفته طول کشیده‌اند
SELECT *
FROM orders
WHERE shipped_date - order_date > INTERVAL '1 week';

این مثال نشان می‌دهد سفارش‌هایی که اختلاف بین shipped_date و order_date آنها بیشتر از یک هفته است، انتخاب می‌شوند.

-- سفارش‌های یک هفته اخیر
SELECT *
FROM orders
WHERE order_date > now() - INTERVAL '1 week';

نمونه میانگین زمان ارسال بر اساس کشور:

SELECT
    ship_country,
    ROUND(AVG((shipped_date - order_date)::numeric), 2) AS avg_days_to_ship
FROM orders
WHERE shipped_date IS NOT NULL
GROUP BY ship_country
ORDER BY avg_days_to_ship;

Derived Data for Reports

می‌توان ستون‌های محاسباتی ایجاد کرد تا اطلاعات زمان‌بندی را دسته‌بندی کنیم:

SELECT
    order_id,
    order_date,
    CASE
        WHEN order_date < '1997-01-01' THEN 'Old Order'
        ELSE 'Recent Order'
    END AS order_period
FROM orders;

مثال تعداد سفارش‌ها بر اساس سال:

SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    COUNT(*) AS total_orders
FROM orders
GROUP BY year
ORDER BY year;

Combining Dates with Aggregates

خلاصه‌های زمانی با ترکیب DATE_TRUNC و توابع تجمعی:

SELECT
    DATE_TRUNC('month', order_date) AS month_start,
    ROUND(SUM(freight)::numeric, 2) AS total_freight
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;

میانگین فریت بر اساس روز هفته:

SELECT
    EXTRACT(DOW FROM order_date) AS day_of_week,
    ROUND(AVG(freight)::numeric, 2) AS avg_freight
FROM orders
GROUP BY day_of_week
ORDER BY day_of_week;

این گزارش‌ها کمک می‌کند ببینیم کدام روز هفته بیشترین میانگین فریت را دارد.

Formatting Dates (Optional)

با TO_CHAR() می‌توانیم تاریخ‌ها را برای نمایش خوانا قالب‌بندی کنیم:

SELECT 
    TO_CHAR(order_date, 'YYYY-MM') AS month_label,
    COUNT(*) AS total_orders
FROM orders
GROUP BY month_label
ORDER BY month_label;

خروجی به صورت “YYYY-MM” نمایش داده می‌شود، مناسب برای گزارش‌گیری انسانی.

Summary