如何在 SWIFT 的 IOS CORE-DATA 请求中使用 SQL GROUP BY 和 SUM 函数? [英] How do I use an SQL GROUP BY and SUM functions in a IOS CORE-DATA request in SWIFT?

问题描述

我有一个表 (Transactions)，其中包含包含 Account_name 和交易金额的记录.我想计算每个账户的所有交易总额，这些交易以私人"开头，交易金额 > 1000.我想按名称按降序对账户进行排序.

I have a table (Transactions) which holds records containing the Account_name and an Amount for the transaction. I would like to calculate the total for all transactions per account which start with 'Private' and for which the transaction amount is > 1000. I would like to get the accounts sorted by name, in a descending order.

所以 SQL 请求应该是这样的:

So the SQL request would be something like this :

    SELECT Account_name, SUM(Amount) 
    FROM Transactions
    WHERE Account_name like 'Private%'
    and Amount > 1000
    GROUP BY Account_name
    ORDER BY Account_name DESC

我将如何在 Swift 中使用 Core-DATA 来做到这一点.

How would I do this using Core-DATA in Swift.

谢谢

推荐答案

请记住，CoreData 不是关系数据库，因此您应该考虑实体而不是表"，而对象不是记录".另请注意，按照惯例，属性名称不应以大写字母开头.也就是说，可以构建一个 fetch 来实现你想要的.关键步骤是:

Bear in mind that CoreData is not a relational database, so you should think of entities not "tables", and objects not "records". Note also that by convention, attribute names should not begin with Uppercase letters. That said, it is possible to construct a fetch to achieve what you want. The key steps are:

1. 创建获取请求
2. 指定一个 NSPredicate 以根据您选择的条件进行过滤
3. 将 resultType 设置为 .DictionaryResultType(Group By"需要)
4. 设置要包含在提取中的属性(为了获取 sum()，这涉及创建一个 NSExpression 和关联的 NSExpressionDescription).
5. 将属性设置为分组依据
6. 创建一个 NSSortDescriptor 以按名称降序排序
7. 执行提取

1. create a fetch request
2. specify an NSPredicate to filter according to your chosen criteria
3. set the resultType to .DictionaryResultType (required for "Group By")
4. set the properties to be included in the fetch (to get the sum(), this involves creating an NSExpression and associated NSExpressionDescription).
5. set the properties to group by
6. Create an NSSortDescriptor to order by name, descending
7. Execute the fetch

查看以下代码:

let fetch = NSFetchRequest(entityName: "Transaction")
let predicate = NSPredicate(format: "Account_name like %@ AND Amount > %@", "Private*",NSNumber(double: 1000.0))
fetch.predicate = predicate
fetch.resultType = .DictionaryResultType
let sumExpression = NSExpression(format: "sum:(Amount)")
let sumED = NSExpressionDescription()
sumED.expression = sumExpression
sumED.name = "sumOfAmount"
sumED.expressionResultType = .DoubleAttributeType
fetch.propertiesToFetch = ["Account_name", sumED]
fetch.propertiesToGroupBy = ["Account_name"]
let sort = NSSortDescriptor(key: "Account_name", ascending: false)
fetch.sortDescriptors = [sort]
let results = managedObjectContext?.executeFetchRequest(fetch, error: nil) as NSArray?

结果将是一个字典数组，键为Account_name"和sumOfAmount".

The result will be an array of dictionaries, with keys "Account_name" and "sumOfAmount".

编辑要提取特定 Account_name 的值，请使用另一个谓词:

EDIT To extract the value for a particular Account_name, use another predicate:

    let newPredicate = NSPredicate(format: "Account_name like %@", "toto")!
    if let resultsArray = results { // to unwrap the optional
        let filteredResults = resultsArray.filteredArrayUsingPredicate(newPredicate)
        let sumOfAmount = (filteredResults[0] as NSDictionary)["sumOfAmount"] as Double
    }

我忽略了上一个示例将 Account_name 过滤到以Private"开头的那些的事实，并假设您将拥有一个(并且只有一个)名为toto"的帐户(因此 filteredResults[0]);您的生产代码应该检查.

I've ignored the fact that the previous example filtered Account_name to those beginning "Private", and assumed you will have one (and only one) account with name "toto" (hence filteredResults[0]); your production code should check.

这篇关于如何在 SWIFT 的 IOS CORE-DATA 请求中使用 SQL GROUP BY 和 SUM 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！