0

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.

3
  • 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.
    – Community Bot
    Aug 23, 2022 at 7:01
  • Where does the service number come from?
    – Vérace
    Aug 23, 2022 at 22:03
  • "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.
    – domiho1
    Aug 23, 2022 at 22:08

1 Answer 1

0

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
;
5
  • 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,
    – domiho1
    Aug 24, 2022 at 9:55
  • @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.)
    – Andriy M
    Aug 24, 2022 at 10:24
  • thanks. will make a EDIT3 above, to accommodate the month pass issue, as is directly related to the initial request
    – domiho1
    Aug 24, 2022 at 11:44
  • @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.
    – Andriy M
    Aug 24, 2022 at 12:25
  • done in dba.stackexchange.com/questions/316015/…. thanks!
    – domiho1
    Aug 24, 2022 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.