要获取每周每个工作日添加的员工数量，可以使用date_trunc()函数按周和工作日对员工进行分组，然后使用count()函数计算每组中的员工数量。类似于：

SELECT
  date_trunc('week', JoiningDate) AS week,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 0) AS sunday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 1) AS monday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 2) AS tuesday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 3) AS wednesday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 4) AS thursday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 5) AS friday,
  count(*) FILTER (WHERE EXTRACT(DOW FROM JoiningDate) = 6) AS saturday,
  count(*) AS total
FROM employee
GROUP BY week
ORDER BY week;

字符串
看看它是否有效！

您可以使用聚合filter子句在单独的列中获取日计数，通过减去1从ISO 8601星期一开始向后移动一天，以查看从星期日开始的周范围，并使用to_char()或设置DateStyle设置以实现所需的日期格式：Demo at db<>fiddle

set DateStyle='SQL,MDY';

SELECT
  to_char("Week",'MM/DD/YYYY')||' - '||("Week"::date+6)::text AS "Week",
  count(*)filter(where dow=0) AS "Sunday",
  count(*)filter(where dow=1) AS "Monday",
  count(*)filter(where dow=2) AS "Tuesday",
  count(*)filter(where dow=3) AS "Wednesday",
  count(*)filter(where dow=4) AS "Thursday",
  count(*)filter(where dow=5) AS "Friday",
  count(*)filter(where dow=6) AS "Saturday",
  count(*) AS "Total"
FROM (SELECT date_trunc('week', "JoiningDate")::date-1 AS "Week", 
             extract(DOW FROM "JoiningDate") dow 
      FROM "Employee")AS a
GROUP BY 1 ORDER BY "Week";

字符串
| 周|周日|周一|周二|周三|周四|周五|周六|总|
| --|--|--|--|--|--|--|--|--|
| 2023年11月26日-2023年2月12日| 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
| 2023年3月12日至9月12日| 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |
| 12/10/2023 - 12/16/2023| 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |
在tablefunc扩展中还有一个crosstab()函数可以自动以这种方式透视结果。

因为我只能使用Postgres 9.5版本，
我们可以在Date上使用DOW得到“DayOfWeek”列，这个dow将有一周中的一天，如Sunday（0）到Saturday（6）。
现在要获得一周的开始和结束日期，我们可以使用INTERVAL类型的[Start of the Week Date]减去“JoiningDate”和“DayOfWeek”列，并将6 -“DayOfWeek”interval添加到“JoiningDate”中，用于[End of the Week Date]也可以使用TO_CHAR的YYYY-MM-DD用于“Week”列。
稍后，为了对查询求和，我们可以在周列中使用Group BY，在“DayOfWeek”列中使用Sum和Case语句来获得每天的员工总数。

SELECT 
    SUM(CASE WHEN ("DayOfWeek" =  0)   THEN "EmployeeCount" ELSE '0' END )  AS "Sun",
    SUM(CASE WHEN ("DayOfWeek" =  1)   THEN "EmployeeCount" ELSE '0' END )  AS "Mon",
    SUM(CASE WHEN ("DayOfWeek" =  2)   THEN "EmployeeCount" ELSE '0' END )  AS "Tue",
    SUM(CASE WHEN ("DayOfWeek" =  3)   THEN "EmployeeCount" ELSE '0' END )  AS "Wed",
    SUM(CASE WHEN ("DayOfWeek" =  4)   THEN "EmployeeCount" ELSE '0' END )  AS "Thr",
    SUM(CASE WHEN ("DayOfWeek" =  5)   THEN "EmployeeCount" ELSE '0' END )  AS "Fri",
    SUM(CASE WHEN ("DayOfWeek" =  6)   THEN "EmployeeCount" ELSE '0' END )  AS "Sat",
    SUM("EmployeeCount")                                                    AS "Total",
    "Week",
FROM (
    SELECT

        -- Week is Sunday to Saturday instead of Monday to Sunday
        -- "2021-07-18 - 2021-07-24" Week
        TO_CHAR("JoiningDate" - (("DayOfWeek") || ' days')::INTERVAL ,'YYYY-MM-DD')
        || ' - ' ||
        TO_CHAR("JoiningDate" + ((6 - "DayOfWeek") || ' days')::INTERVAL ,'YYYY-MM-DD') AS "Week",

        "DayOfWeek",
        COUNT("Id")  AS "EmployeeCount"
    FROM (
        SELECT
            "Id",  TO_TIMESTAMP("JoiningEpoch") AT TIME ZONE 'America/Chicago' ) AS "JoiningDate",

            -- The day of the week as Sunday (0) to Saturday (6), Using INTEGER instead of dayname to increase Perfomance
            EXTRACT(DOW FROM DATE( TO_TIMESTAMP("JoiningEpoch") AT TIME ZONE 'America/Chicago' ))   AS "DayOfWeek",   -- 0-6  DayNumber
        FROM "Employee"
    ) AS sq
    GROUP BY 1,2
) AS sq1
GROUP BY "Week"
ORDER BY "Week"

字符串