如何在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.我希望按帐户名称降序排列帐户。

因此，SQL请求将如下所示：

  SELECT Account_name，SUM（金额） 
 FROM Transactions 
 WHERE Account_name like'Private％'
 and Amount> 1000 
 GROUP BY Account_name 
 ORDER BY Account_name DESC 
  

我该怎么做在Swift中使用Core-DATA。

感谢解决方案

请注意，CoreData不是关系数据库，因此您应该将实体视为不是表，对象不是记录。另请注意，按照惯例，属性名称不应以大写字母开头。也就是说，可以构建一个获取来实现你想要的。关键步骤是：
$ b $ ol

创建一个获取请求

指定一个 NSPredicate 根据您选择的条件过滤

将 resultType 设置为。 DictionaryResultType （必须为Group By）

设置要包含在fetch中的属性（以获取sum（），这涉及到创建一个 NSExpression 和相关的 NSExpressionDescription ）。

>

创建一个 NSSortDescriptor 以按名称降序排列

执行获取

请参阅以下代码：

  let fetch = NSFetchRequest （entityName：Transaction）
 let predicate = NSPredicate（format：Account_name like％@ AND Amount>％@，Private *，NSNumber（double：1000.0））
 fetch.predicate =谓词
 fetch.resultType = .DictionaryResultType 
让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，升序：false）
 fetch.sortDescriptors = [sort] 
 let results = managedObjectContext？.executeFetchRequest（fetch，error：nil）as NSArray？ 
  

结果将是一个字典数组，其中键为Account_name和sumOfAmount p>

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

  let newPredicate = NSPredicate（格式：Account_name like％@，toto）！ 
 if resultArray = results {//解开可选的
 let filteredResults = resultArray.filteredArrayUsingPredicate（newPredicate）
 let sumOfAmount =（filteredResults [0] as NSDictionary）[sumOfAmount] as Double 
} 
  

我忽略了前一个例子过滤 Account_name 开头为Private，并假设您将有一个（且只有一个）名称为toto的帐户（因此 filteredResults [0] ）;您的生产代码应该检查。

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.

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

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

Thanks

解决方案

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. 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

See the following code:

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?

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

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
    }

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屋！