Group by week - day that starts the week

Question

Group by week - day that starts the week

Navigation

#1 by (2 votes)

4

The scenario is that I have some events a day, and I have to group them per week, in such a way that they can be agurped such that:

SELECT array_agg(a_day) as data,  week
FROM (
       SELECT day::date as a_day, date_trunc('week', day) :: date as week
       from generate_series(now(), (now() - interval '168 days') ::timestamp,
                            '- 1 day') as day
  ) as p
GROUP BY week
ORDER BY  week DESC

And this day has an output of:

[
  {
    "data": [
      2018-11-29,
      2018-11-28,
      2018-11-27,
      2018-11-26
    ],
    "week": "2018-11-26"
  },
  {
    "data": [
      2018-11-25,
      2018-11-24,
      2018-11-23,
      2018-11-22,
      2018-11-21,
      2018-11-20,
      2018-11-19
    ],
    "week": "2018-11-19"
  },
  {
    "data": [
      2018-11-18,
      2018-11-17,
      2018-11-16,
      2018-11-15,
      2018-11-14,
      2018-11-13,
      2018-11-12
    ],
    "week": "2018-11-12"
  },
  {
    "data": [
      2018-11-11,
      2018-11-10,
      2018-11-09,
      2018-11-08,
      2018-11-07,
      2018-11-06,
      2018-11-05
    ],
    "week": "2018-11-05"
  },
  {
    "data": [
      2018-11-04,
      2018-11-03,
      2018-11-02,
      2018-11-01,
      2018-10-31,
      2018-10-30,
      2018-10-29
    ],
    "week": "2018-10-29"
  },
  {
    "data": [
      2018-10-28,
      2018-10-27,
      2018-10-26,
      2018-10-25,
      2018-10-24,
      2018-10-23,
      2018-10-22
    ],
    "week": "2018-10-22"
  },
  {
    "data": [
      2018-10-21,
      2018-10-20,
      2018-10-19,
      2018-10-18,
      2018-10-17,
      2018-10-16,
      2018-10-15
    ],
    "week": "2018-10-15"
  },
  {
    "data": [
      2018-10-14,
      2018-10-13,
      2018-10-12,
      2018-10-11,
      2018-10-10,
      2018-10-09,
      2018-10-08
    ],
    "week": "2018-10-08"
  },
  {
    "data": [
      2018-10-07,
      2018-10-06,
      2018-10-05,
      2018-10-04,
      2018-10-03,
      2018-10-02,
      2018-10-01
    ],
    "week": "2018-10-01"
  },
  {
    "data": [
      2018-09-30
    ],
    "week": "2018-09-24"
  }
]

Up here well. The problem comes from the user can choose the beginning of week, being the established by Postgres on Monday, and by most users on Sunday (but this implies that it can be a Thursday or a Wednesday).

I have the values:

+-----------+-------+
|    Dia    | Valor |
+-----------+-------+
| Domingo   |     1 |
| Lunes     |     2 |
| Martes    |     3 |
| Miercoles |     4 |
| Jueves    |     5 |
| Viernes   |     6 |
| Sabado    |     7 |
+-----------+-------+

where: company_date_start would be one of these values

select array_agg(a_day) as data, week
FROM (
       select day::date as a_day, (date_trunc('week', day) - (:company_date_start || ' days')::interval) :: date as week
       from generate_series(now(), (now() - interval '60 days') ::timestamp,
                            '- 1 day') as day
     ) as p
GROUP BY week
ORDER BY week DESC

If it is Sunday the beginning of the week, it is more or less well, but on Sundays they are left behind. And if for example it's a Tuesday, it would appear as a week on Friday and to group or talk.

It really is only grouped by weeks giving the day of the week begins. so it would be grouped at intervals of 7 days.

I'm using Postgres 11 and here you have a Fiddle

Thanks in advance

sql postgresql

asked by Txmx 29.11.2018 в 14:25

source

1 answer

Value returned by a simple order (128 + n) How to capture html attribute in a js variable and then assign it in css variable?

score 2 · Accepted Answer

After a few days of headache, I've taken it out:

SELECT array_agg(a_day) as data, week
FROM (
       SELECT day_g::date as a_day,

              CASE
                WHEN day_g - ((EXTRACT(DOW FROM day_g) + 1) - (first_day_week) || ' DAYS')::interval > day_g
                  THEN
                    day_g - ((EXTRACT(DOW FROM day_g) + 1) - (first_day_week) + 7 || ' DAYS')::interval
                ELSE
                    day_g - ((EXTRACT(DOW FROM day_g) + 1) - (first_day_week) || ' DAYS')::interval
                END       as week
       from generate_series(now(), (now() - interval '168 days') ::timestamp,
                            '- 1 day') as day_g
              INNER JOIN user c ON c.id = 1
     ) as p
GROUP BY week
ORDER BY week DESC;

It's a bit complex to explain. But I'm going to try. Knowing that the EXTRACT(DOW FROM day_g) will give you the number of the week with these values:

+-----------+-------+
|    DOW    |  Dia  | 
+-----------+-------+
| Domingo   |     0 |
| Lunes     |     1 |
| Martes    |     2 |
| Miércoles |     3 |
| Jueves    |     4 |
| Viernes   |     5 |
| Sábado    |     6 |
+-----------+-------+

Then, you can square it with the table by adding +1 (in my case, if you have other values you can add or subtract what is convenient). So now the DOW it gives you will be the same as your values per week.

Then, if the generated day you subtract the difference between the first day of the week chosen by the user ( first_day_week ) and its value in DOW, you get the first_day_week nearest.

If, for example, your user says that they are the beginning of the week on Wednesdays, first_day_week = 5 .

monday   2018-12-03 - ((1+ 1)- 5) = 2018-12-06
sunday   2018-12-02 - ((0+ 1)- 5) = 2018-12-06
saturday 2018-12-01 - ((6+ 1)- 5) = 2018-11-29
friday   2018-11-30 - ((5+ 1)- 5) = 2018-11-29
thursday 2018-11-29 - ((4+ 1)- 5) = 2018-11-29
wednesday2018-11-28 - ((3+ 1)- 5) = 2018-11-29
tuesday  2018-11-27 - ((2+ 1)- 5) = 2018-11-29
monday   2018-11-26 - ((1+ 1)- 5) = 2018-11-29
sunday   2018-11-25 - ((0+ 1)- 5) = 2018-11-29
saturday 2018-11-24 - ((6+ 1)- 5) = 2018-11-22
friday   2018-11-23 - ((5+ 1)- 5) = 2018-11-22
thursday 2018-11-22 - ((4+ 1)- 5) = 2018-11-22
wednesday2018-11-21 - ((3+ 1)- 5) = 2018-11-22
tuesday  2018-11-20 - ((2+ 1)- 5) = 2018-11-22

Here is a problem because:

monday   2018-12-03 - ((1+ 1)- 5) = 2018-12-06
sunday   2018-12-02 - ((0+ 1)- 5) = 2018-12-06

They can not be within the week of December 6, it has to be in the previous week. So if the resulting date is larger than the date generated, you have to subtract one week. (in my case add because I am in descending time).

I hope you have explained me well.