Geek asks and answers

Compare one row with another in SQLite

I have an android app. In my Android application, I have a SQLite database. In my SQLite database, I have a table that looks like this:

_id abcd
 1 1 1 1 0
 2 0 1 1 1
 3 1 0 0 1
 4 0 1 0 1

I want to calculate for each row: of all columns a, b, c and d that have 1 in EITHER the current or previous row, what percentage has 1 in BOTH of the current and previous row? The result will look like this:

_id abcd result
 1 1 1 1 0 NULL
 2 0 1 1 1 50%
 3 1 0 0 1 25%
 4 0 1 0 1 33%

I can do it in Java outside of SQLite, but I would rather do it in SQL; that would be more neat. Which query should I use?

+3
source share
1 answer
Select  CurAndNext.T1_id
    , Sum( Case When D1.Val + D2.Val = 1 Then 1 End ) As CntInEitherRow
    , Sum( Case When D1.Val + D2.Val = 2 Then 1 End ) / 4.000 As PercBoth
From    (
        Select T1._id As T1_id, Max( T2._id ) As T2_id
        From MyTable As T1
            Left Join MyTable As T2
                On T2._id < T1._id
        Group By T1._id
        ) As CurAndNext
    Join    (
            Select _id, 'a' As Col, a As Val From MyTable As T1
            Union All
            Select _id, 'b', b From MyTable As T1
            Union All
            Select _id, 'c', c From MyTable As T1
            Union All
            Select _id, 'd', d From MyTable As T1
            ) As D1
        On D1._id = CurAndNext.T1_id
    Left Join   (
                Select _id, 'a' As Col, a As Val From MyTable As T1
                Union All
                Select _id, 'b', b From MyTable As T1
                Union All
                Select _id, 'c', c From MyTable As T1
                Union All
                Select _id, 'd', d From MyTable As T1
                ) As D2
            On D2._id = CurAndNext.T2_id
                And D2.Col = D1.Col
Group By CurAndNext.T1_Id

A significant factor hampering this query is data denormalization. So I need to normalize it to get the information you are looking for.

Knowing what the columns a, b, cand drepresent the difference in the world. The complexity of the above query indicates a scheme that does not reflect business needs well. Knowing that they represent student attendance, we can develop an alternative scheme.

Create Table Student
    (
    Id int not null Primary Key
    , Name varchar(50) not null
    )

Create Table Class
    (
    Id int not null Primary Key
    , Name varchar(50) not null
    )

-- if using dates, this would be the equivalent
-- of a calendar table
Create Table ClassDay 
    ( 
    DayNum int not null Primary Key 
    )

-- ClassDayNum would be better as a Date    
Create Table Attendence
    (
    StudentId int References Student( Id )
    , ClassId int References Class( Id )
    , ClassDayNum int not null  References ClassDay( DayNum )
    , Unique( StudentId, ClassId, ClassDayNum )
    )

Insert Student( Id, Name )
Select 1, 'a'
Union All Select 2, 'b'
Union All Select 3, 'c'
Union All Select 4, 'd'

Insert Class( Id, Name )
Values (1, 'Some Class' )

Insert ClassDay( DayNum )
Select 1
Union All Select 2
Union All Select 3
Union All Select 4

Insert Attendence( ClassId, StudentId, ClassDay )
Select 1, 1, 1
Union All Select 1, 1, 3
Union All Select 1, 2, 1
Union All Select 1, 2, 2
Union All Select 1, 2, 4
Union All Select 1, 3, 1
Union All Select 1, 3, 2
Union All Select 1, 4, 2
Union All Select 1, 4, 3
Union All Select 1, 4, 4

all columns a, b, c and d that have 1 in EITHER the current or previous row

, , , , .

Select Class.Id, ClassDay.DayNum
    , Count(Distinct A.StudentId) As Attendence
    , Count(Distinct A.StudentId) / 4.000 As Ratio
From Class
    Cross Join Student
    Cross Join ClassDay
    Left Join Attendence As A
        On A.ClassId = Class.Id
            And A.StudentId = Student.Id
            And A.ClassDayNum = ClassDay.DayNum
            And A.ClassDayNum > 1
    Left Join Attendence As A2
        On A2.ClassId = Class.Id
            And A2.StudentId = Student.Id
            And A2.ClassDayNum = ClassDay.DayNum - 1
Where Not( A.StudentId Is Not Null And A2.StudentId Is Not Null )
Group By Class.Id, ClassDay.DayNum

:

DayNum  Attendence | Ratio
1      |    0      |   0
2      |    1      |   .25
3      |    1      |   .25
4      |    1      |   .25

1 BOTH

Select ClassDay.DayNum
    , Sum( Case When A.StudentId Is Not Null And A2.StudentId Is Not Null Then 1 End )
    , Sum( Case When A.StudentId Is Not Null And A2.StudentId Is Not Null Then 1 End ) / 4.000
From Class
    Cross Join Student
    Cross Join ClassDay
    Left Join Attendence As A
        On A.ClassId = Class.Id
            And A.StudentId = Student.Id
            And A.ClassDayNum = ClassDay.DayNum
            And A.ClassDayNum > 1
    Left Join Attendence As A2
        On A2.ClassId = Class.Id
            And A2.StudentId = Student.Id
            And A2.ClassDayNum = ClassDay.DayNum - 1
Group By ClassDay.DayNum            

DayNum | Attendence | Ratio
1      |    NULL    |  NULL
2      |    2       |  0.500000
3      |    1       |  0.250000
4      |    1       |  0.250000
+3

More articles:


All Articles