مطالعه بیشتر

استفاده از SQL برای گروه‌بندی نتایج بر اساس یک دوره زمانی، مقایسه مجموع‌های هفته به هفته، و یافتن مدت زمان بین دو تاریخ.

کار با تاریخ‌ها در SQL

ما از طریق سه سناریوی رایج هنگام کار با تاریخ‌ها در SQL راه می‌رویم. از پایگاه داده نمونه همراه با متابیس استفاده می‌کنیم تا بتوانید دنبال کنید، و به برخی توابع و تکنیک‌های SQL رایج که در بسیاری از پایگاه‌های داده کار می‌کنند پایبند می‌مانیم. فرض می‌کنیم این اولین پرس‌وجوی SQL شما نیست و می‌خواهید سطح را بالا ببرید. اما حتی اگر تازه شروع کرده‌اید، باید بتوانید چند نکته یاد بگیرید.

این مقاله به مجموعه داده نمونه از پیش تعریف شده متکی است، اما همچنین می‌توانید داده تمرینی خود را با استفاده از تولیدکننده مجموعه داده AI تولید کنید.

Scenario	Example
گروه‌بندی نتایج بر اساس یک دوره زمانی	چند نفر در هر هفته حساب ایجاد کردند؟
مقایسه مجموع‌های هفته به هفته	شمارش سفارش‌ها این هفته چگونه با هفته گذشته مقایسه شد؟
یافتن مدت زمان بین دو تاریخ	چند روز بین زمانی که یک مشتری حساب ایجاد کرد و زمانی که اولین سفارش خود را ثبت کرد؟

گروه‌بندی نتایج بر اساس یک دوره زمانی

اغلب می‌خواهیم سؤال‌هایی مثل این بپرسیم: چند مشتری در هر ماه ثبت‌نام کردند؟ یا چند سفارش در هر هفته ثبت شد؟ در اینجا از طریق یک جدول نتایج می‌رویم، ردیف‌ها را می‌شماریم و آن شمارش‌ها را بر اساس یک دوره زمانی گروه‌بندی می‌کنیم.

مثال: چند نفر در هر هفته حساب ایجاد کردند؟

در اینجا می‌خواهیم دو ستون برگردانیم:

| WEEK | ACCOUNTS CREATED |
|------|------------------|
| ...  | ...              |

بیایید به جدول People خود نگاه کنیم. می‌توانیم SELECT * FROM people LIMIT 1 را برای دیدن فهرست فیلدها اجرا کنیم، یا به سادگی روی آیکون کتاب کلیک کنیم تا متادیتا درباره جداول در پایگاه داده‌ای که با آن کار می‌کنیم ببینیم.

از آنجایی که به زمانی که یک مشتری برای حساب ثبت‌نام کرد علاقه‌مندیم، به فیلد created_at نیاز داریم، که طبق مرجع داده ما "تاریخی که رکورد کاربر ایجاد شد. همچنین به عنوان 'تاریخ عضویت' کاربر اشاره می‌شود".

باید این ایجاد حساب‌ها را گروه‌بندی کنیم، اما به جای گروه‌بندی بر اساس تاریخ، باید آن‌ها را بر اساس هفته گروه‌بندی کنیم. برای دیدن اینکه هر تاریخ created_at در کدام هفته قرار می‌گیرد، از تابع DATE_TRUNC استفاده می‌کنیم.

DATE_TRUNC به شما امکان می‌دهد timestampهای خود را به دانه‌بندی که به آن اهمیت می‌دهید گرد کنید ("truncate"): هفته، ماه و غیره. DATE_TRUNC دو آرگومان می‌گیرد: متن و یک timestamp، و یک timestamp برمی‌گرداند. آن آرگومان متن اول دوره زمانی است، در این مورد 'week'، اما می‌توانستیم دانه‌بندی‌های مختلفی مثل month، quarter یا year مشخص کنیم (مستندات پایگاه داده خود را درباره DATE_TRUNC بررسی کنید تا گزینه‌ها را ببینید). برای هدف ما در اینجا، DATE_TRUNC('week', created_at) را می‌نویسیم، که تاریخ دوشنبه هر هفته را برمی‌گرداند. به هر حال، SQL به حروف بزرگ و کوچک حساس نیست، پس می‌توانید کد خود را هر طور که دوست دارید case کنید (date_trunc هم کار می‌کند، یا DaTe_TrUnc اگر به طور طعنه‌آمیز پرس‌وجو می‌کنید).

همچنین از نام‌های مستعار برای نتایج برای دادن نام‌های خاص‌تر به ستون‌ها استفاده می‌کنیم. به عنوان مثال، با استفاده از کلمه کلیدی AS، Count(*) را تغییر می‌دهیم تا به عنوان accounts_created نمایش داده شود.

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS accounts_created
FROM
  people
GROUP BY
  week
ORDER BY
  week

که برمی‌گرداند:

| WEEK    | ACCOUNTS_CREATED |
|---------|------------------|
| 4/18/16 | 13               |
| 4/25/16 | 17               |
| 5/2/16  | 17               |
| ...     | ...              |

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

که تقریباً همانطور که از یک مجموعه داده تصادفی انتظار داریم به نظر می‌رسد.

مقایسه مجموع‌های هفته به هفته

اغلب می‌خواهید ببینید یک شمارش چگونه از یک هفته به هفته بعد تغییر کرده است، که می‌توانید با join کردن یک جدول به خودش و مقایسه هر هفته با هفته قبلی آن محاسبه کنید.

مثال: سفارش‌ها چگونه با هفته گذشته مقایسه شدند؟

آنچه در اینجا به دنبال آن هستیم هفته، شمارش سفارش‌ها برای آن هفته، و تغییر هفته به هفته (آیا سفارش‌ها بالا رفت، پایین آمد، یا ثابت ماند؟) است:

| WEEK    | COUNT_OF_ORDERS | WOW_CHANGE |
|---------|-----------------|------------|
| ...     | ...             | ...        |

برای دریافت این داده، ابتدا باید یک جدول که شمارش سفارش‌ها در هر هفته را فهرست می‌کند دریافت کنیم. اساساً همان کاری که برای جدول People انجام دادیم را انجام می‌دهیم، اما این بار برای جدول Orders: از DATE_TRUNC برای گروه‌بندی شمارش سفارش‌ها بر اساس هفته استفاده می‌کنیم.

SELECT
  DATE_TRUNC('week', orders.created_at) AS week,
  COUNT(*) AS order_count
FROM
  orders
GROUP BY
  week

که به ما می‌دهد:

| WEEK     | ORDER_COUNT |
|----------|-------------|
| 7/1/2019 | 115         |
| 7/2/2018 | 119         |
| 7/3/2017 | 78          |
| ...      | ...         |

از این نتایج برای ساخت بقیه پرس‌وجو استفاده می‌کنیم. آنچه باید انجام دهیم این است که شمارش سفارش از هر هفته (که به آن w1 اشاره می‌کنیم) را بگیریم و آن را از شمارش هفته قبل (که w2 می‌نامیم) کم کنیم. چالش اینجا این است که، برای انجام تفریق، باید به نوعی شمارش هر هفته را در همان ردیف با شمارش هفته قبل دریافت کنیم.

در اینجا نحوه انجام آن:

نتایج خود را در یک عبارت جدول مشترک (CTE) بپیچید.
آن CTE را به خودش join کنید با offset کردن join به 1 هفته
شمارش کل سفارش هفته قبل را از کل هر هفته کم کنید تا تغییر هفته به هفته را دریافت کنید

پرس‌وجوی بالا را با استفاده از کلمه کلیدی WITH به یک عبارت جدول مشترک (CTE) تبدیل می‌کنیم. اساساً CTEها راهی برای اختصاص یک متغیر به نتایج موقت هستند، که سپس می‌توانیم با آن‌ها طوری رفتار کنیم که گویی نتایج یک جدول واقعی در پایگاه داده بودند (مثل Orders یا Table). جدول نتایج را order_count_by_week می‌نامیم. سپس از این جدول استفاده می‌کنیم و آن را به خودش join می‌کنیم، اما با یک offset: ردیف‌های آن به یک هفته جابجا شده.

در اینجا پرس‌وجو با join offset:

WITH order_count_by_week AS (
  SELECT
    DATE_TRUNC('week', orders.created_at) AS week,
    COUNT(*) AS order_count
  FROM
    orders
  GROUP BY
    week
)

SELECT
  *
FROM
  order_count_by_week w1
  LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)
ORDER BY
  w1.week

این پرس‌وجو نتیجه می‌دهد:

| WEEK      | ORDER_COUNT | WEEK      | ORDER_COUNT |
|-----------|-------------|-----------|-------------|
| 4/25/2016 | 1           |           |             |
| 5/2/2016  | 3           | 4/25/2016 | 1           |
| 5/9/2016  | 3           | 5/2/2016  | 3           |
| ...       | ...         | ...       | ...         |

بیایید آنچه در اینجا اتفاق می‌افتد را باز کنیم. CTE order_count_by_week را به عنوان w1 نام مستعار دادیم، و سپس دوباره به عنوان w2. بعد، آن دو CTE را left-join کردیم. کلید اینجا تابع DATEADD است، که برای افزودن یک هفته به هر مقدار w2.week برای offset کردن ستون‌های join شده استفاده کردیم:

LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)

تابع DATEADD یک دوره زمانی (WEEK)، تعداد آن هفته‌ها برای اعمال (در این مورد 1، چون می‌خواهیم تفاوت از یک هفته پیش را بدانیم)، و ستون تاریخ برای اعمال جمع به (w2.week) می‌گیرد. (توجه داشته باشید که برخی پایگاه‌های داده از INTERVAL به جای DATEADD استفاده می‌کنند، مثل w2.week + INTERVAL '1 week'). این ردیف‌ها را "هم‌تراز می‌کند"، اما با یک هفته offset (توجه داشته باشید عدم وجود مقادیر در گروه دوم هفته/شمارش سفارش برای آن ردیف اول بالا).

حالا یک جدول با همه چیزهایی که برای محاسبه تغییر هفته به هفته در هر ردیف نیاز داریم داریم. حالا فقط باید عبارت select خود را تغییر دهیم تا ستون‌هایی که به دنبال آن هستیم را برگرداند:

هفته‌ای که سفارش‌ها ثبت شدند
شمارش سفارش‌ها برای آن هفته
تغییر هفته به هفته (یعنی تفاوت بین شمارش این هفته و هفته قبل).

در اینجا پرس‌وجوی کامل:

WITH order_count_by_week AS (
  SELECT
    DATE_TRUNC('week', orders.created_at) AS week,
    COUNT(*) AS order_count
  FROM
    orders
  GROUP BY
    week
)

SELECT
  w1.week,
  w1.order_count AS count_of_orders,
  w1.order_count - w2.order_count AS wow_change
FROM
  order_count_by_week w1
  LEFT JOIN order_count_by_week w2 ON w1.week = DATEADD(WEEK, 1, w2.week)
ORDER BY
  w1.week

که برمی‌گرداند:

| WEEK    | COUNT_OF_ORDERS | WOW_CHANGE |
|---------|-----------------|------------|
| 4/25/16 | 1               |            |
| 5/2/16  | 3               | 2          |
| 5/9/16  | 3               | 0          |
| ...     | ...             | ...        |

یافتن مدت زمان بین دو تاریخ

اغلب می‌خواهید مقدار زمان بین دو رویداد را پیدا کنید: تعداد ثانیه بین ثبت‌نام و تسویه حساب، یا تعداد روز بین تسویه حساب و تحویل.

مثال: چند روز بین زمانی که یک مشتری حساب ایجاد کرد و زمانی که اولین سفارش خود را ثبت کرد؟

برای پاسخ به این، بیایید چهار ستون برگردانیم:

ID مشتری
تاریخ ایجاد حساب مشتری
تاریخی که آن مشتری اولین سفارش خود را ثبت کرد
تفاوت بین آن دو تاریخ

حالا، برای دریافت این اطلاعات، باید داده را از جداول People و Orders بگیریم. اما نمی‌خواهیم این دو جدول را join کنیم، چون فقط به اولین سفارش هر مشتری نیاز داریم.

بیایید با یافتن زمانی که هر مشتری اولین سفارش خود را ثبت کرد شروع کنیم.

SELECT
  user_id,
  MIN(created_at) as first_order_date
FROM
  orders
GROUP BY
  user_id

در اینجا سفارش‌ها را بر اساس مشتری (GROUP BY user_id) گروه‌بندی می‌کنیم و از تابع MIN برای یافتن تاریخ اولیه سفارش استفاده می‌کنیم. آن نتایج را به عنوان first_orders ذخیره می‌کنیم و با پرس‌وجوی خود ادامه می‌دهیم.

WITH first_orders AS (
  SELECT
    user_id,
    MIN(created_at) as first_order_date
  FROM
    orders
  GROUP BY
    user_id
)

SELECT
  people.id,
  people.created_at AS account_creation,
  first_orders.first_order_date,
  DATEDIFF(
    'day', people.created_at, first_orders.first_order_date
  ) AS days_before_first_order
FROM
  PEOPLE
  JOIN first_orders ON first_orders.user_id = people.id
ORDER BY
  account_creation

که به ما می‌دهد:

| ID   | ACCOUNT_CREATION | FIRST_ORDER_DATE | DAYS_BEFORE_FIRST_ORDER |
|------|------------------|------------------|-------------------------|
| 915  | 4/19/16 21:35    | 10/9/16 8:42     | 173                     |
| 1712 | 4/21/16 23:46    | 8/15/16 4:01     | 116                     |
| 2379 | 4/22/16 4:07     | 5/22/16 3:56     | 30                      |
| ...  | ...              | ...              | ...                     |

برای خلاصه: تاریخ created_at مشتری را گرفتیم و پرس‌وجو را به CTE خود join کردیم. از تابع DATEDIFF برای یافتن تعداد روز بین ایجاد حساب و اولین سفارش آن‌ها استفاده کردیم، سپس نتیجه را به عنوان days_before_first_order ذخیره کردیم. DATEDIFF یک دوره زمانی (مثل "day"، "week"، "month") می‌گیرد و تعداد دوره‌ها بین دو timestamp را برمی‌گرداند.

(با توجه به اینکه پایگاه داده نمونه تصادفی است، پاسخ‌های ما با واقعیت خیلی خوب مطابقت ندارند—چقدر مشتریان بین راه‌اندازی حساب و خرید 173 روز صبر می‌کنند؟)