我有一个 Android 应用程序。在我的 Android 应用程序中，我有一个 SQLite 数据库。在我的 SQLite 数据库中，我有一个如下所示的表:

_id    a    b    c    d
 1     1    1    1    0
 2     0    1    1    1
 3     1    0    0    1
 4     0    1    0    1

我想为每一行计算:在当前行或上一行中都为 1 的所有 a、b、c 和 d 列中，当前行和上一行中都为 1 的百分比是多少？输出看起来像这样:

_id    a    b    c    d    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%

我可以在 SQLite 之外的 Java 中执行此操作，但我宁愿在 SQL 中执行此操作；这样会更整洁。我应该使用什么查询？

最佳答案

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、b、c 和 d 代表什么列，这对世界来说意义重大。上述查询的复杂性表明模式不能很好地映射到业务需求。知道它们代表学生出勤率，我们可以设计一个替代模式。

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

of all the columns a, b, c and d that have a 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

what percentage have a 1 in BOTH the current and previous row

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

关于android - SQLite 中一行与另一行的比较，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6287053/