英文:
Kotlin Exposed Sum on condition (CaseWhenElse)
问题
我有一个数据结构如下的表格：
id type cityName regDate
1249 0 城市1 2019-10-01
我想要以数据类对象的列表形式获取每个月独特城市的注册数量结果输出，数据类对象如下所示：
```kotlin
data class NewClientsNumberCityMonth(
val cityName: String = "",
val 一月新客户数量: Int = 0,
val 二月新客户数量: Int = 0,
val 三月新客户数量: Int = 0,
val 四月新客户数量: Int = 0,
val 五月新客户数量: Int = 0,
val 六月新客户数量: Int = 0,
val 七月新客户数量: Int = 0,
val 八月新客户数量: Int = 0,
val 九月新客户数量: Int = 0,
val 十月新客户数量: Int = 0,
val 十一月新客户数量: Int = 0,
val 十二月新客户数量: Int = 0,
val 总数: Int = 0
)
并将这些对象用作填充表格的字符串（我们不知道独特城市的数量），结果应该如下所示：
城市1 5 8 3 1 2 1 4 1 2 1 0 0
城市2 69 23 7 5 3 10 24 14 12 23 25 10
...
我正在尝试以下代码：
val tempMutList = mutableListOf<NewClientsNumberCityMonthModel>()
transaction(Connection.TRANSACTION_SERIALIZABLE, 2) {
addLogger(StdOutSqlLogger)
ClientsDataExposed
.slice(ClientsDataExposed.cityName)
.selectAll()
.groupBy(ClientsDataExposed.cityName)
.map { it[ClientsDataExposed.cityName] }
.toList().forEach {
val regsInCity = ClientsDataExposed
.slice(
ClientsDataExposed.id,
ClientsDataExposed.cityName,
ClientsDataExposed.type,
ClientsDataExposed.regDate,
ClientsDataExposed.regDate.month()
)
.selectAll()
.andWhere { ClientsDataExposed.cityName.eq(it) }
.andWhere {
ClientsDataExposed.regDate.between(
Date.valueOf("2019-01-01".toString()),
Date.valueOf("2020-01-01".toString())
)
}
.andWhere {
(ClientsDataExposed.type.inList(contracterTypeSelectedCheckboxes.filterValues { it }.keys.toList())) and (ClientsDataExposed.cityName.inList(
citiesSelectedChekboxes.filterValues { it }.keys.toList()
))
}
.map { it[ClientsDataExposed.regDate.month()] }
.toList()
val cityClientsPerMonth = NewClientsNumberCityMonthModel(
it,
regsInCity.count { it == 1 },
regsInCity.count { it == 2 },
regsInCity.count { it == 3 },
regsInCity.count { it == 4 },
regsInCity.count { it == 5 },
regsInCity.count { it == 6 },
regsInCity.count { it == 7 },
regsInCity.count { it == 8 },
regsInCity.count { it == 9 },
regsInCity.count { it == 10 },
regsInCity.count { it == 11 },
regsInCity.count { it == 12 },
regsInCity.count()
)
tempMutList.add(cityClientsPerMonth)
}
viewTableTabOfnewClientsNumberCityMonth.value = tempMutList.map { it }
}
我知道我应该使用Sum()和CaseWhenElse，就像这里中所示，来检查ClientsDataExposed.regDate.month()，它会给出一个月份的数字（1-12），以将Sum()的结果分配给我的数据类属性，但我找不到任何CaseWhenElse语法的示例，也无法自己弄清楚，或者可能有其他方法可以在不使用CaseWhenElse的情况下获取它吗？
<details>
<summary>英文:</summary>
I have a table with data structure like :
id type cityName regDate
1249 0 City1 2019-10-01
I want to get result output of unique cities and the number of registrations in it per month as a list of data class objects of
data class NewClientsNumberCityMonth(
val cityName: String = "",
val januaryNewClientsNumber :Int = 0,
val februaryNewClientsNumber :Int = 0,
val marchNewClientsNumber :Int = 0,
val aprilNewClientsNumber :Int = 0,
val mayNewClientsNumber :Int = 0,
val juneNewClientsNumber :Int = 0,
val julyNewClientsNumber :Int = 0,
val augustNewClientsNumber :Int = 0,
val septemberNewClientsNumber :Int = 0,
val octoberNewClientsNumber :Int = 0,
val novemberNewClientsNumber :Int = 0,
val decemberNewClientsNumber :Int = 0
val total :Int = 0
)
and use this objects as the strings to fill the table (we dont know the number of unique cities),
result should be like :
City1 5 8 3 1 2 1 4 1 2 1 0 0
City2 69 23 7 5 3 10 24 14 12 23 25 10
...
im trying this
val tempMutList = mutableListOf<NewClientsNumberCityMonthModel>()
transaction(Connection.TRANSACTION_SERIALIZABLE, 2) {
addLogger(StdOutSqlLogger)
ClientsDataExposed
.slice(ClientsDataExposed.cityName)
.selectAll()
.groupBy(ClientsDataExposed.cityName)
.map { it[ClientsDataExposed.cityName] }
.toList().forEach {
val regsInCity = ClientsDataExposed
.slice(
ClientsDataExposed.id,
ClientsDataExposed.cityName,
ClientsDataExposed.type,
ClientsDataExposed.regDate,
ClientsDataExposed.regDate.month()
)
.selectAll()
.andWhere { ClientsDataExposed.cityName.eq(it) }
.andWhere {
ClientsDataExposed.regDate.between(
Date.valueOf("2019-01-01".toString()),
Date.valueOf("2020-01-01".toString())
)
}
.andWhere {
(ClientsDataExposed.type.inList(contracterTypeSelectedCheckboxes.filterValues { it }.keys.toList())) and (ClientsDataExposed.cityName.inList(
citiesSelectedChekboxes.filterValues { it }.keys.toList()
))
}
.map { it[ClientsDataExposed.regDate.month()] }
.toList()
val cityClientsPerMonth = NewClientsNumberCityMonthModel(
it,
regsInCity.count { it == 1 },
regsInCity.count { it == 2 },
regsInCity.count { it == 3 },
regsInCity.count { it == 4 },
regsInCity.count { it == 5 },
regsInCity.count { it == 6 },
regsInCity.count { it == 7 },
regsInCity.count { it == 8 },
regsInCity.count { it == 9 },
regsInCity.count { it == 10 },
regsInCity.count { it == 11 },
regsInCity.count { it == 12 },
regsInCity.count()
)
tempMutList.add(cityClientsPerMonth)
//obj of dataclass
}
viewTableTabOfnewClientsNumberCityMonth.value = tempMutList.map { it }
}
i know, that i should use Sum() and CaseWhenElse , like [here][1] , with check on
```ClientsDataExposed.regDate.month() ```
which gives a month number (1-12), to assign the sum() result to my data class property, but i can't find any examples of CaseWhenElse syntax and i can't figure out it myself,
or may be there is another way of getting it whithout using of CaseWhenElse ?
[1]: https://stackoverflow.com/questions/9888263/count-records-for-every-month-in-a-year/9888368#9888368
</details>
# 答案1
**得分**: 4
请检查以下代码：
```kotlin
val monthExpr = ClientsDataExposed.regDate.month()
fun sumByMonth(monthNum: Int) = Expression.build {
val caseExpr = case().
When(monthExpr eq intLiteral(monthNum), intLiteral(1)).
Else(intLiteral(0))
Sum(caseExpr, IntegerColumnType())
}
val janSumExp = sumByMonth(1)
val febSumExp = sumByMonth(2)
ClientsDataExposed.
slice(ClientsDataExposed.cityName, janSumExp, febSumExp).
selectAll().
groupBy(ClientsDataExposed.id).map {
val janSum = it[janSumExp]
val febSum = it[febSumExp]
...
}
希望这能帮助您检查您的代码。
英文:
Please check that code:
val monthExpr = ClientsDataExposed.regDate.month()
fun sumByMonth(monthNum: Int) = Expression.build {
val caseExpr = case().
When(monthExpr eq intLiteral(monthNum), intLiteral(1)).
Else(intLiteral(0))
Sum(caseExpr, IntegerColumnType())
}
val janSumExp = sumByMonth(1)
val febSumExp = sumByMonth(2)
ClientsDataExposed.
slice(ClientsDataExposed.cityName, janSumExp, febSumExp).
selectAll().
groupBy(ClientsDataExposed.id).map {
val janSum = it[janSumExp]
val febSum = it[febSumExp]
...
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论