CODEWITHSUNDEEP
sqlhiveql
Danny David Leybzon
Danny David Leybzon

Reputation: 680

How can I get a view of the changes between rows?

I have a table that looks like

object_id   attribute_1    attribute_2    attribute_3
1           a              a              a
1           a              a              a
1           a              b              a
1           a              a              a
2           z              z              z
2           w              z              z

I want to understand what kinds of changes users are making to these objects. One potential end result could be a table of objects and the number of times there attributes have been changed, along with the total number of times the object is logged, like so:

object_id   attribute_1    attribute_2    attribute_3   count
1           0              2              0             3
2           1              0              0             2

How would I accomplish this? What are some other potential ways to quantify and understand what changes are being made?

Upvotes: 1

Views: 32

Answers (1)

Jandisson
Jandisson

Reputation: 380

If you do not need to count as a change when a attribute is changed back to a previous value then you could group by object_id and attribute_N to obtain the number of diferent values of this attribute.

I would recommend you to write a application or a procedure in order to calculate what you want. But if you want to solve the problem with just one query, it would be something like that:

SELECT object_id,
       sum(changes_attribute_1) AS changes_attribute_1,
       sum(changes_attribute_2) AS changes_attribute_2
FROM
  ( SELECT object_id,
           sum(IF(atribute = 2,changes,0)) AS changes_attribute_2,
           sum(IF(atribute = 1,changes,0)) AS changes_attribute_1
   FROM
     (SELECT count(*)-1 AS changes,
             object_id,
             1 AS atribute
      FROM
        (SELECT count(*),
                object_id
         FROM TABLE AS inner_table
         GROUP BY object_id,
                  attribute_1) AS tudo
      GROUP BY object_id
      UNION ALL SELECT count(*)-1 AS changes,
                       object_id,
                       2 AS atribute
      FROM
        (SELECT count(*),
                object_id
         FROM TABLE AS inner_table
         GROUP BY object_id,
                  attribute_2) AS tudo
      GROUP BY object_id) AS tudo
   GROUP BY object_id,
            atribute) AS tudo2
GROUP BY object_id

I have tried the query with this table:

object_id, attribute_1, attribute_2
1, 1, 1
1, 2, 1
1, 3, 2
2, 1, 1
2, 2, 2
2, 3, 4

And I had this result

# object_id, changes_attribute_1, changes_attribute_2
1, 2, 1
2, 2, 2

Upvotes: 1

Related Questions