如何在Spark SQL v2.4中计算给定日期的上季度首日

Hey there! For Spark SQL v2.4, we can use built-in date functions to easily calculate the first day of the previous quarter for any given date, matching your quarter rules (Q1: Jan-Mar, Q2: Apr-Jun, Q3: Jul-Sep, Q4: Oct-Dec). Here are two reliable approaches:

Approach 1: Use date_trunc + add_months (Simplest Method) #

This method leverages Spark's built-in functions to first get the start of the current quarter, then subtract 3 months to jump back to the start of the previous quarter.

SQL Query #

SELECT
  input_date,
  date_format(
    add_months(date_trunc('quarter', input_date), -3),
    'yyyy-MM-dd'
  ) AS start_date
FROM your_table_name;

Breakdown: #

1. date_trunc('quarter', input_date): Truncates the input date to the first day of its current quarter. For example, 2020-01-21 becomes 2020-01-01, 2020-10-20 becomes 2020-10-01.
2. add_months(..., -3): Subtracts 3 months from the current quarter's start date. This lands us exactly on the first day of the previous quarter.
3. date_format(..., 'yyyy-MM-dd'): Ensures the result is formatted as a standard date string (optional if your output accepts the date type directly).

Test with Your Examples: #

Running this query against your sample inputs will produce exactly the expected results:

Approach 2: Case Statement (Explicit Logic) #

If you prefer more explicit control over the quarter logic, you can use a CASE statement to calculate the year and month of the previous quarter's start, then construct the date.

SQL Query #

SELECT
  input_date,
  date_format(
    concat(
      -- Determine the year of the previous quarter
      CASE quarter(input_date)
        WHEN 1 THEN year(input_date) - 1
        ELSE year(input_date)
      END,
      '-',
      -- Determine the month of the previous quarter's first day
      CASE quarter(input_date)
        WHEN 1 THEN '10'
        WHEN 2 THEN '01'
        WHEN 3 THEN '04'
        WHEN 4 THEN '07'
      END,
      '-01'
    ),
    'yyyy-MM-dd'
  ) AS start_date
FROM your_table_name;

Breakdown: #

• For Q1 (Jan-Mar), the previous quarter is Q4 of the prior year (Oct-Dec), so we subtract 1 from the year and use month 10.
• For Q2 (Apr-Jun), previous quarter is Q1 of the same year (Jan-Mar), use month 01.
• For Q3 (Jul-Sep), previous quarter is Q2 of the same year (Apr-Jun), use month 04.
• For Q4 (Oct-Dec), previous quarter is Q3 of the same year (Jul-Sep), use month 07.
• We concatenate the year, month, and day 01, then format it as a date.

Both methods work perfectly in Spark SQL 2.4. The first approach is cleaner and more maintainable, while the second gives you direct visibility into the quarter logic.

内容的提问来源于stack exchange，提问作者dexter80