Counting the amount of times a boolean goes from True to False and False to True in a column in presto

user_6265575 0
Viewed 296

I have the following table with three columns : Id, timestamp , fact

ID Timestamp Fact
1 2021-10-25 11:21:12 false
2 2021-10-14 18:49:25 false
2 2021-11-03 12:48:47 true
2 2021-11-08 23:15:12 false
2 2021-11-08 23:15:30 true
3 2021-10-07 04:06:08 false
3 2021-10-07 07:47:43 true
3 2021-10-07 07:49:56 false
3 2021-10-07 07:51:35 false
8 2021-10-06 15:36:46 false
8 2021-10-06 15:37:12 false
9 2021-10-07 07:13:27 false
9 2021-10-07 07:15:07 true
9 2021-10-07 07:17:33 false
10 2021-10-06 14:03:57 true
10 2021-10-06 14:10:45 false

I want to write a query which gives me the following outcome:

  • per each id , it gives first and last fact value and when they happened
  • per each id , how often fact has been changed from True to False or False to True
  • per each id, count of fact

Then the outcome would be:

ID first_fact first_iteration last_fact last_iteration chang_in_fact iterations
1 false 2021-10-25 11:21:12 false 2021-10-25 11:21:12 0 1
2 false 2021-10-14 18:49:25 true 2021-11-08 23:15:30 3 4
3 false 2021-10-07 04:06:08 false 2021-10-07 07:51:35 2 4
8 false 2021-10-06 15:36:46 false 2021-10-06 15:37:12 0 2
9 false 2021-10-07 07:13:27 false 2021-10-07 07:17:33 2 3
10 true 2021-10-06 14:03:57 false 2021-10-06 14:10:45 1 2

How can I do that?

presto iteration sql
1 Answers

In presto you can use lag operator to get previous then use it to compare with "current" and then group everything by id and get needed facts with max/min_by :

WITH dataset(id, Timestamp, Fact) AS (
    values (1, timestamp '2021-10-25 11:21:12', false),
        (2, timestamp '2021-10-14 18:49:25', false),
        (2, timestamp '2021-11-03 12:48:47', true),
        (2, timestamp '2021-11-08 23:15:12', false),
        (2, timestamp '2021-11-08 23:15:30', true),
        (3, timestamp '2021-10-07 04:06:08', false),
        (3, timestamp '2021-10-07 07:47:43', true),
        (3, timestamp '2021-10-07 07:49:56', false),
        (3, timestamp '2021-10-07 07:51:35', false),
        (8, timestamp '2021-10-06 15:36:46', false),
        (8, timestamp '2021-10-06 15:37:12', false),
        (9, timestamp '2021-10-07 07:13:27', false),
        (9, timestamp '2021-10-07 07:15:07', true),
        (9, timestamp '2021-10-07 07:17:33', false),
        (10, timestamp '2021-10-06 14:03:57', true),
        (10, timestamp '2021-10-06 14:10:45', false)
)

SELECT id,
    min_by(fact, Timestamp) first_fact,
    min(Timestamp) first_iter,
    max_by(fact, Timestamp) last_fact,
    max(Timestamp) last_iter,
    sum(changed) chang_in_fact,
    count(*) as iter
FROM(
        SELECT id,
            Timestamp,
            Fact,
            case
                when prev_fact != fact then 1 else 0
            end as changed
        FROM(
                SELECT *,
                    lag(fact) over (
                        partition by id
                        order by timestamp
                    ) as prev_fact
                FROM dataset
            )
    )
GROUP BY id
ORDER BY id

Output:

id first_fact first_iter last_fact last_iter chang_in_fact iter
1 false 2021-10-25 11:21:12.000 false 2021-10-25 11:21:12.000 0 1
2 false 2021-10-14 18:49:25.000 true 2021-11-08 23:15:30.000 3 4
3 false 2021-10-07 04:06:08.000 false 2021-10-07 07:51:35.000 2 4
8 false 2021-10-06 15:36:46.000 false 2021-10-06 15:37:12.000 0 2
9 false 2021-10-07 07:13:27.000 false 2021-10-07 07:17:33.000 2 3
10 true 2021-10-06 14:03:57.000 false 2021-10-06 14:10:45.000 1 2
user_25012790
Related