Question 206276 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL Calculate Multi Rows

rid    mak_id       Machine prosessdate       m30 door clamp cutting
507191  552140800   GT2600L 12-7-2023 10:10:13  0   0   0   0
507192  552140800   GT2600L 12-7-2023 10:10:14  0   0   0   1
507193  552140800   GT2600L 12-7-2023 10:10:15  0   0   0   1
507194  552140800   GT2600L 12-7-2023 10:10:16  0   0   0   1
507196  552140800   GT2600L 12-7-2023 10:10:17  0   0   0   0
507197  552140800   GT2600L 12-7-2023 10:10:18  0   0   0   0
507199  552140800   GT2600L 12-7-2023 10:10:19  0   0   0   0
507201  552140800   GT2600L 12-7-2023 10:10:20  1   0   0   0
507202  552140800   GT2600L 12-7-2023 10:10:21  1   0   0   0
507204  552140800   GT2600L 12-7-2023 10:10:22  1   0   0   0
507205  552140800   GT2600L 12-7-2023 10:10:23  1   0   0   0
507207  552140800   GT2600L 12-7-2023 10:10:24  1   0   0   0
507209  552140800   GT2600L 12-7-2023 10:10:25  1   0   0   0
507210  552140800   GT2600L 12-7-2023 10:10:26  1   0   0   0
507212  552140800   GT2600L 12-7-2023 10:10:27  1   0   0   0
507214  552140800   GT2600L 12-7-2023 10:10:28  1   0   0   0
507215  552140800   GT2600L 12-7-2023 10:10:29  1   0   0   0
507217  552140800   GT2600L 12-7-2023 10:10:30  1   0   0   0
507219  552140800   GT2600L 12-7-2023 10:10:31  1   0   0   0
507221  552140800   GT2600L 12-7-2023 10:10:32  1   0   0   0
507223  552140800   GT2600L 12-7-2023 10:10:33  1   0   0   0
507225  552140800   GT2600L 12-7-2023 10:10:34  1   1   0   0
507226  552140800   GT2600L 12-7-2023 10:10:35  1   1   0   0
507228  552140800   GT2600L 12-7-2023 10:10:36  1   1   0   0
507230  552140800   GT2600L 12-7-2023 10:10:37  1   1   1   0
507231  552140800   GT2600L 12-7-2023 10:10:38  1   1   1   0
507232  552140800   GT2600L 12-7-2023 10:10:39  1   1   1   0
507234  552140800   GT2600L 12-7-2023 10:10:40  1   1   1   0
507236  552140800   GT2600L 12-7-2023 10:10:41  1   1   1   0
507237  552140800   GT2600L 12-7-2023 10:10:42  1   1   1   0
507238  552140800   GT2600L 12-7-2023 10:10:43  1   1   0   0
507239  552140800   GT2600L 12-7-2023 10:10:44  1   0   0   0
507240  552140800   GT2600L 12-7-2023 10:10:45  1   0   0   0
507241  552140800   GT2600L 12-7-2023 10:10:46  0   0   0   0
507243  552140800   GT2600L 12-7-2023 10:10:47  0   0   0   0
507245  552140800   GT2600L 12-7-2023 10:10:48  0   0   0   1
507247  552140800   GT2600L 12-7-2023 10:10:49  0   0   0   1
507249  552140800   GT2600L 12-7-2023 10:10:50  0   0   0   1

Final Output:

Machine     Status  StartDate           EndDate             Difference
552140800   M30  12-7-2023 10:10:14      12-7-2023 10:10:20 00:00:06
552140800   Door    12-7-2023 10:10:20  12-7-2023 10:10:34  00:00:14
552140800   Clamp   12-7-2023 10:10:34  12-7-2023 10:10:37  00:00:03
552140800   Cutting 12-7-2023 10:10:37  12-7-2023 10:10:48  00:00:11

Hİ! I am using PosgreSQL

I want to see the time interval of cutting and m30 condition.

  • When first 1 comes from cutting condition then first 1 comes from m30 condition I Need this interval time,
  • I Need the time interval of m30 and door condition,
  • I Need the time interval ofdoor and clamp condition,
  • I Need the time interval of clamp and cutting

I make a sample FINAL OUTPUT above…..

I have a 100k row dataset which is constantly building with more repeat data on customers.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you Jan ,

    select mak_id as machine,
'M30' AS Status,
min(prosessdate) as StartDate
from tbl
where cutting = 1
group by mak_id

    but your code is not correct. This code total record count 6. I have a 100k-150k row dataset


  2. 0

    Your initial table is tbl, then

    with m30s as (
    select mak_id as machine,
    'M30' AS Status,
    min(prosessdate) as StartDate
    from tbl
    where cutting = 1
    group by mak_id
),
m30e as (
    select mak_id as machine,
    'M30' AS Status,
    min(prosessdate) as EndDate
    from tbl
    where m30 = 1
    group by mak_id
),
doors as (
    select mak_id as machine,
    'door' AS Status,
    min(prosessdate) as StartDate
    from tbl
    where m30 = 1
    group by mak_id
),
doore as (
    select mak_id as machine,
    'door' AS Status,
    min(prosessdate) as EndDate
    from tbl
    where door = 1
    group by mak_id
),
clamps as (
    select mak_id as machine,
    'clamp' AS Status,
    min(prosessdate) as StartDate
    from tbl
    where door = 1
    group by mak_id
),
clampe as (
    select mak_id as machine,
    'clamp' AS Status,
    min(prosessdate) as EndDate
    from tbl
    where clamp = 1
    group by mak_id
),
cuttings as (
    select mak_id as machine,
    'cutting' AS Status,
    min(prosessdate) as StartDate
    from tbl
    where clamp = 1
    group by mak_id
),
cuttingehlp as (
    select mak_id as machine,
    'cutting' AS Status,
    prosessdate as EndDate
    from tbl
    where cutting = 1
)

select machine, status, startdate, min(enddate) as enddate, min(enddate) - startdate as difference
from
(
    select t.* from
    (
        select m30s.machine, m30s.status, m30s.startdate, m30e.enddate,
        m30e.enddate - m30s.startdate as difference
        from m30s
        left join m30e on m30s.status = m30e.status

        union

        select doors.machine, doors.status, doors.startdate, doore.enddate,
        doore.enddate - doors.startdate as difference
        from doors
        left join doore on doors.status = doore.status

        union

        select clamps.machine, clamps.status, clamps.startdate, clampe.enddate,
        clampe.enddate - clamps.startdate as difference
        from clamps
        left join clampe on clamps.status = clampe.status

        union

        select cuttings.machine, cuttings.status, cuttings.startdate, cuttingehlp.enddate,
        cuttingehlp.enddate - cuttings.startdate as difference
        from cuttings
        left join cuttingehlp on cuttings.status = cuttingehlp.status
    ) t

    where startdate < enddate
) tb
group by machine, status, startdate
order by startdate

    yields

    +===========+=========+=====================+=====================+============+
| machine   | status  | startdate           | enddate             | difference |
+===========+=========+=====================+=====================+============+
| 552140800 | M30     | 2023-12-07 10:10:14 | 2023-12-07 10:10:20 | 00:00:06   |
| 552140800 | door    | 2023-12-07 10:10:20 | 2023-12-07 10:10:34 | 00:00:14   |
| 552140800 | clamp   | 2023-12-07 10:10:34 | 2023-12-07 10:10:37 | 00:00:03   |
| 552140800 | cutting | 2023-12-07 10:10:37 | 2023-12-07 10:10:48 | 00:00:11   |
+-----------+---------+---------------------+---------------------+------------+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search