Add value on one column based on values of a group of rows

Question

Imagine something like "uber Eats":

there is a driver's base (mall), other restaurants outside the mall and customers locations.

Each row is a travel between two points (base, restaurant_# and customer #) : can have services with 3 rows (base-restaurant-customer-base) other with 2 (base-customer-base), other with a little more.... (wrong order :)) of fuel stop.

Need to aggregate for each service the rows applicable to that service in a column, for example service_numb with a serial number starting each month and the month, 1/month,2/month ,3/month.

there is a column:

• Base, where "OUT" and "IN" indicate leaving base and returning base, so this should be the trigger for a new service.
• dateline, for sequencing/order
• cart, for identifying the delivering cart, among many

this is only to be run one time to populate that column, instead of doing it manually.

is possible in postgresSQL 13.6 ?

EDIT:

With a table:

datetime	cart	base
2022 08 23 10:10	aa	OUT
2022 08 23 10:20	aa
2022 08 23 10:30	aa	IN
2022 08 23 10:05	bb	OUT
2022 08 23 10:10	bb
2022 08 23 10:30	bb
2022 08 23 10:35	bb	IN
2022 09 23 10:10	aa	OUT
2022 09 23 10:20	aa
2022 09 23 10:30	aa	IN

needed to create an additional column with the service number, year/month and by cart, like:

datetime	cart	base	service_num
2022 08 23 10:10	aa	OUT	aa/202208/1
2022 08 23 10:20	aa	aa/202208/1
2022 08 23 10:30	aa	IN	aa/202208/1
2022 08 23 10:05	bb	OUT	bb/202208/1
2022 08 23 10:10	bb	bb/202208/1
2022 08 23 10:30	bb	bb/202208/1
2022 08 23 10:35	bb	IN	bb/202208/1
2022 09 23 10:10	aa	OUT	aa/202209/1
2022 09 23 10:20	aa	aa/202209/1
2022 09 23 10:30	aa	IN	aa/202209/1

EDIT2:

SELECT *,SUM(CASE WHEN base='OUT' then 1 end) OVER (PARTITION BY cart ORDER BY datetime) AS m1
FROM table
ORDER BY datetime

already gives a sequential number across all the table, divided by cart.

missing: add cart, yyyy/mm and restart the numbering each month and by cart

EDIT3:

using the provided answer below by @Andriy M, is already working, the only issue is in the cases where one or more of the legs "jump" to the next month?

example: leg1 2022年07月31日 23:30 leg2 2022年08月01日 00:05 leg3 2022年08月01日 00:35

for the same service_number. The datetime, is already timeline, so no issue there. The question seems to be on the PARTITION BY of the datetime, to accomodate the passage to a new month.

• Community: Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
• Vérace: Where does the service number come from?
• domiho1: "service number" is the objective... with the code of EDIT2, have a column with 1,1,1,2,2,2,2,3,3,3 for that table.

Answer 1

Your service_num values seem to consist of these three components delimited by a /:

• cart;
• expression representing the month of datetime formatted as YYYYMM;
• expression enumerating OUT/IN sequences.

Nothing needs to be done about cart, you just use it in the final concatenation expression.

Regarding the formatted month, you can obtain it as

to_char(datetime, 'YYYYMM')

And as for the sequence numbers, your SUM expression is a good start. You need to additionally partition by months in order for the numbers to reset each month. You can use the above month expression as the second partition criterion:

SUM(CASE WHEN base='OUT' THEN 1 END)
OVER (PARTITION BY cart, to_char(datetime, 'YYYYMM') ORDER BY datetime)

Or you could use the date_trunc function instead, which might or might not be faster:

SUM(CASE WHEN base='OUT' THEN 1 END)
OVER (PARTITION BY cart, date_trunc('month', datetime) ORDER BY datetime)

With all the components now covered, you just need to concatenate them into a single service_num value:

SELECT
 *
, cart
 || '/'
 || to_char(datetime, 'YYYYMM')
 || '/'
 || SUM(CASE WHEN base='OUT' THEN 1 END)
 OVER (PARTITION BY cart, date_trunc('month', datetime) ORDER BY datetime)
 AS service_num
FROM
 YourTable
ORDER BY
 datetime
;

• domiho1: thanks, it works. Any idea how to cover the case when one or more of the legs "jump" to the next month? ex: leg1 2022年07月31日 23:30 leg2 2022年08月01日 00:05 leg3 2022年08月01日 00:35, for the same service_number. The datetime, is allready timeline, so no issue there. The question seems to be on the PARTITION BY of the datetime,
• Andriy M: @domiho1: Not at the moment, no. If I did have one, it would be better to post it as a separate solution to a separate question though, so as not to turn this one into a mess. (If/when you decide to submit this as a new question, please make sure to explain what you want the output for such cases to look like.)
• domiho1: thanks. will make a EDIT3 above, to accommodate the month pass issue, as is directly related to the initial request
• Andriy M: @domiho1: I think this question is good as it is. Your additional stipulation makes it a new problem really, even if related to the original one. Therefore, it would be better to create a new thread instead of editing this question.
• domiho1: done in dba.stackexchange.com/questions/316015/…. thanks!