mysql時分




MySQL查詢GROUP BY日/月/年 (9)

是否有可能我做了一個簡單的查詢來計算我在確定的時間段內有多少條記錄,如年,月或日,具有TIMESTAMP字段,如:

SELECT COUNT(id)
FROM stats
WHERE record_date.YEAR = 2009
GROUP BY record_date.YEAR

甚至:

SELECT COUNT(id)
FROM stats
GROUP BY record_date.YEAR, record_date.MONTH

有每月統計。

謝謝!


.... group by to_char(date, 'YYYY') - > 1989

.... group by to_char(date,'MM') - > 05

.... group by to_char(date,'DD') ---> 23

.... group by to_char(date,'MON') ---> 5月

.... group by to_char(date,'YY') ---> 89


以下查詢適用於Oracle Database 12c版本12.1.0.1.0中的我

SELECT COUNT(*)
FROM stats
GROUP BY 
extract(MONTH FROM TIMESTAMP),
extract(MONTH FROM TIMESTAMP),
extract(YEAR  FROM TIMESTAMP);

如果你想篩選特定年份的記錄(例如2000),那麼優化WHERE子句,如下所示:

SELECT MONTH(date_column), COUNT(*)
FROM date_table
WHERE date_column >= '2000-01-01' AND date_column < '2001-01-01'
GROUP BY MONTH(date_column)
-- average 0.016 sec.

代替:

WHERE YEAR(date_column) = 2000
-- average 0.132 sec.

結果是針對包含300k行和日期列索引的表生成的。

至於GROUP BY子句,我針對上述表格測試了三個變體; 結果如下:

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY YEAR(date_column), MONTH(date_column)
-- codelogic
-- average 0.250 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY DATE_FORMAT(date_column, '%Y%m')
-- Andriy M
-- average 0.468 sec.

SELECT YEAR(date_column), MONTH(date_column), COUNT(*)
FROM date_table
GROUP BY EXTRACT(YEAR_MONTH FROM date_column)
-- fu-chi
-- average 0.203 sec.

最後一個是贏家。


如果您想獲得按月份排序的每月每月行數的月度統計數據,請嘗試以下操作:

SELECT count(id),
      YEAR(record_date),
      MONTH(record_date) 
FROM `table` 
GROUP BY YEAR(record_date),
        MONTH(record_date) 
ORDER BY YEAR(record_date) DESC,
        MONTH(record_date) DESC

完整且簡單的解決方案,具有類似的性能,但更短,更靈活,

SELECT COUNT(*) FROM stats
-- GROUP BY YEAR(record_date), MONTH(record_date), DAYOFMONTH(record_date)
GROUP BY DATE_FORMAT(record_date, '%Y-%m-%d')

您可以在GROUP BY中簡單地使用Mysql DATE_FORMAT()函數。 在某些情況下,您可能需要添加額外的列以增加清晰度,例如記錄跨越數年,然後同一月發生在不同的年份。有多種選項可以自定義此選項。 請閱讀此開始。 希望它對你有很大的幫助。 以下是您理解的示例查詢

SELECT
    COUNT(id),
    DATE_FORMAT(record_date, '%Y-%m-%d') AS DAY,
    DATE_FORMAT(record_date, '%Y-%m') AS MONTH,
    DATE_FORMAT(record_date, '%Y') AS YEAR,

FROM
    stats
WHERE
    YEAR = 2009
GROUP BY
    DATE_FORMAT(record_date, '%Y-%m-%d ');

我更喜歡優化一年的組選擇,如下所示:

SELECT COUNT(*)
  FROM stats
 WHERE record_date >= :year 
   AND record_date <  :year + INTERVAL 1 YEAR;

通過這種方式,您可以只用一個命名參數來綁定一年,例如'2009' ,並且不必擔心分別添加'-01-01'或傳入'2010'

另外,據推測,我們只是計數行和id從不為NULL ,我更喜歡COUNT(*) COUNT(id)


試試這個

SELECT COUNT(id)
FROM stats
GROUP BY EXTRACT(YEAR_MONTH FROM record_date)

EXTRACT(unit FROM date)函數效果更好,因為使用的分組少了,函數返回一個數字值。

分組時的比較條件將比DATE_FORMAT函數(返回字符串值)更快。 嘗試使用返回SQL字符串比較條件(WHERE,HAVING,ORDER BY,GROUP BY)的非字符串值的函數|字段。






group-by