PGSQL统计常用

1、可以使用COALESCE函数将NULL值替换为0。COALESCE函数接受多个参数，返回第一个非NULL参数。例如，假设我们要查询test表中的number字段，如果该字段为NULL，则将其替换为0，可以使用以下查询语句：

SELECT COALESCE(number, 0) FROM test;

这将返回一个包含test表中所有number字段的值的结果集，如果字段为NULL，则将其替换为0。

2、

	SELECT 
    to_char(t.day, 'MM.DD') AS day, 
    COALESCE(COUNT(m.id), 0) AS count 
FROM 
    generate_series(CURRENT_DATE - INTERVAL '6 days', CURRENT_DATE, '1 day') AS t(day) 
LEFT JOIN 
    manage_api_call as m ON DATE_TRUNC('day', m.create_time)=t.day
GROUP BY 
    day 
ORDER BY 
    day ASC
		
		
		 SELECT DATE_FORMAT(@cdate := date_add(@cdate, INTERVAL - 1 month), '%m') MONTH
        FROM (SELECT @cdate := date_add(CURDATE(), INTERVAL 1 month) FROM blade_user LIMIT 12) a
		
		
		
		SELECT to_char(year_month, 'MM') AS month, COUNT(*) AS count
FROM (SELECT generate_series(cast('2023-01-01' as date), cast('2023-12-01' as date), '1 month') year_month) months
GROUP BY month
ORDER BY month;


SELECT generate_series(cast('2023-01-01' as date), cast('2023-12-01' as date), '1 month') year_month

SELECT generate_series(CURRENT_DATE - INTERVAL '11 months', CURRENT_DATE, '1 month') year_month