QUARTER별 쿼리 만들기 ( 쿼터별 리포트 )

MySQL 쿼터별로 그룹핑 리포팅

 

QUARTER() 함수

 

QUARTER 함수

 

SELECT YEAR(leaddate) AS year 
	,QUARTER(leaddate) AS quarter
	,COUNT(jobid) AS jobcount
FROM jobs
WHERE contactid = '19249'
GROUP BY YEAR(leaddate), QUARTER(leaddate)
ORDER BY YEAR(leaddate), QUARTER(leaddate)

 

출처: http://stackoverflow.com/questions/6067526/how-do-i-group-a-date-field-to-get-quarterly-results-in-mysql

 

 

예제

SELECT 
	YEAR(A.BIZ_DATE) AS YEAR 
	,CASE QUARTER(A.BIZ_DATE) 
		WHEN 1 THEN "Q1" 
		WHEN 2 THEN "Q2" 
		WHEN 3 THEN "Q3" 
		WHEN 4 THEN "Q4" 
		END AS date  /* QUARTER */
	,COUNT(P.PURCHASE_AMT) AS NumOfSales 
FROM SALE_HD A 
	INNER JOIN PAYMENT P ON A.SALE_ID = P.SALE_ID 
WHERE A.BIZ_DATE BETWEEN "20200101" AND "20211014"     
GROUP BY YEAR(A.BIZ_DATE), QUARTER(A.BIZ_DATE)
ORDER BY YEAR(A.BIZ_DATE), QUARTER(A.BIZ_DATE);

============================
YEAR	date	NumOfSales
2021	Q3	1579
2021	Q4	268

(참고: MSSQL에서는 쿼터별 함수는 DATEPART(QUARTER, A.BIZ_DATE)로 사용합니다)

 

 

 

[ MySQL 개발 문서 ]

 

QUARTER(date)

Returns the quarter of the year for date, in the range 1 to 4.

mysql> SELECT QUARTER('2008-04-01'); -> 2

 

[ Quarter - Q1, Q2, Q3, Q4  쿼러란 무엇인가? ]

 

What is a 'Quarter - Q1, Q2, Q3, Q4'

A quarter (Q1, Q2, Q3, Q4) is a three-month period on a financial calendar that acts as a basis for the reporting of earnings and the paying of dividends. A quarter refers to one-fourth of a year and is typically expressed as "Q." The four quarters that make up the year are: January, February and March (Q1); April, May and June (Q2); July, August and September (Q3); and October, November and December (Q4). A quarter is often shown with its relevant year, as in Q1 2015 or Q1/15, which represents the first quarter of the year 2015.

Read more: Quarter - Q1, Q2, Q3, Q4 Definition | Investopedia http://www.investopedia.com/terms/q/quarter.asp#ixzz4Lmn6QX65 
Follow us: Investopedia on Facebook