CODEWITHSUNDEEP
postgresql
Mauritz Hansen
Mauritz Hansen

Reputation: 4774

How to group by date close together?

In have a table with rows presenting dog matings. Often matings occur more than once in a female heat cycle, so matings with the same female and heats close together in time would signify a single heat cycle. For instance:

Date         Female
----------   ---------
2019-03-28   Female1
2019-04-01   Female1
2019-04-03   Female1
2019-10-15   Female2
2019-10-16   Female2
2019-10-19   Female1
2019-10-23   Female1

In the table you can see three mating groupings, 2 for Female1 and 1 for Female2. I want to select these rows and in my results add a column that will identify the matings of the different groupings as belonging together. The first and last dates in a grouping is separated by no more than 10 days.

I have tried converting the date field to epoch seconds and then dividing by some number. This gives me something but with many errors in the groupings.

Upvotes: 0

Views: 404

Answers (1)

Akina
Akina

Reputation: 42622

WITH cte AS (
SELECT "Date",
       "Female",
-- assume that the lag is not more than 7 days
       CASE WHEN ("Date" - LAG("Date") OVER (PARTITION BY "Female" ORDER BY "Date")) <= 7
            THEN 0
            ELSE 1 END "is it mating start?"
FROM matings
)
SELECT "Date",
       "Female",
       SUM("is it mating start?") OVER (PARTITION BY "Female" ORDER BY "Date") "mating number"
FROM cte
ORDER BY "Female", "Date";

fiddle

Upvotes: 1

Related Questions