postgresql - 追加一个字符串

Question

我正在构建一个图表，需要显示给定月份的所有日期以提交申请。在某些日子里，可能有 5 个，而在其他日子里可能有 0 个。我正在从应用程序表中获取数据，但我需要为没有值的日期附加字符串。例如，今天是 05/29，有 3 人申请。 05/21 有 2 人申请。在此期间(和之前)没有其他人申请。所以我需要获取这些日期并为它们存储 0 值。

下面是我用来恢复和分组数据的方法:

SELECT DISTINCT to_char ("applicationdate",'mm') AS applicationmonth
      , to_char ("applicationdate",'dd') AS applicationdate
      , to_char ("applicationdate", 'yyyy') AS applicationyear
      , term
      , COUNT(*) AS count
FROM  campapplications.basketball
WHERE term = 'SU' 
GROUP BY applicationdate, term
ORDER BY applicationdate, term

在一天结束时，我正在做一个插入。

我已经看过其他地方，但除了使用 listAppend() 函数之外无法真正获得任何输入。

Answer 1

尚不清楚“附加字符串”如何与图表或插入相关...但是，只关注查询，日历表或 CTE可用于生成特定日期范围内的所有日期。 (对于较大的数据集，日历表通常更好)。确定日期后，outer join他们回到您的应用程序表以获取每个日期的计数。

您没有提到您使用的是哪个 DBMS，但从语法上我猜是 Oracle。假设“applicationDate”列仅包含一个日期(没有时间)，下面的查询将返回给定范围内所有日期的计数:#startDate# 和 #endDate#。根据需要调整日期。

SQL: http://sqlfiddle.com/#!4/c09877/2

WITH CTE(TheDate) AS
(
    SELECT FromDate+level-1
    FROM (
        SELECT <cfqueryparam value="#startDate#" cfsqltype="cf_sql_date"> AS FromDate
               , <cfqueryparam value="#endDate#" cfsqltype="cf_sql_date">  AS ToDate 
        FROM   dual
     ) d
     CONNECT BY LEVEL <=  ToDate - FromDate + 1
)
SELECT TheDate
      , COUNT(b.applicationDate) AS count
FROM   cte LEFT JOIN basketball b ON b.applicationDate = cte.TheDate
GROUP BY TheDate
ORDER BY TheDate      
;

示例结果:

|              THEDATE | COUNT |
|----------------------|-------|
| 2019-05-21T00:00:00Z |     2 |
| 2019-05-22T00:00:00Z |     0 |
| 2019-05-23T00:00:00Z |     0 |
| 2019-05-24T00:00:00Z |     0 |
| 2019-05-25T00:00:00Z |     0 |
| 2019-05-26T00:00:00Z |     0 |
| 2019-05-27T00:00:00Z |     0 |
| 2019-05-28T00:00:00Z |     0 |
| 2019-05-29T00:00:00Z |     3 |

更新:

我不太擅长 PostgreSQL，但是 this thread显示如何生成日期范围。只需将硬编码日期替换为 cfqueryparam。

SQL: http://sqlfiddle.com/#!15/2a0a7/4

SELECT TheDate
      , COUNT(b.applicationDate) AS count
FROM  generate_series
        ( '2019-05-21'::date 
        , '2019-05-29'::date
        , '1 day'::interval) TheDate 
       LEFT JOIN basketball b ON b.applicationDate = TheDate
GROUP BY TheDate
ORDER BY TheDate