F#查询，按单列分组多个值 [英] F# Query, Group Multiple Values by Single Column

问题描述

我有一个F#sql查询，该查询需要对每个组中的两列求和.

I have an F# sql query which needs to sum two columns in each group.

let financials = query{
        for data in dbData do
        groupValBy (data.earning, data.losses) data.store into group
        select (group.Key, group.Sum(fun (group) -> fst group), group.Sum(fun (group) -> snd group))
    }

我还没有找到执行此操作的任何示例，但是此代码可以很好地编译，并且从各种迹象来看，它都应该可以工作.但是，在执行时，我总是会收到此消息无法格式化节点'New'以执行为SQL的消息"的异常.如果我删除data.losses一切正常，但添加第二个值会导致错误.还有另一种方法吗?建议将不胜感激.

I have not been able to find any examples of how to do this, however this code compiles fine, and by every indication, it should work. However when executing I always get an exception with this message "Could not format node 'New' for execution as SQL." If I remove data.losses everything works fine, but adding the second value causes an error. Is there another way to do this? Suggests would be appreciated.

请注意，此问题类似，但处理了将多个列分组的相反情况:

Note, this question is similar, but handles the reverse situation in which multiple columns are grouped: groupby multiple columns in a F# 3.0 query

更新:

我也尝试了以下方法:

(NewAnonymousObjectHelper(T(data.earning, data.losses)))

其中T是:

type T(earning : decimal, losses : decimal) =
    member val Earning=earning with get,set
    member val Losses=losses with get,set

但是，随后需要创建一个新的Sum函数来接受类型T.我也尝试过使用F#PowerPack中的MutableTuple，但未能使其识别MutableTuple(对此代码/dll和关联的命名空间不太熟悉) ).但似乎应该可以:

However then a new Sum function would need to be created accepting the type T. I've also tried using MutableTuple from the F# PowerPack but failed to get it to recognize MutableTuple (not very familiar with this code/dll and associated namespaces). But it seems like it should work:

let t = MutableTuple<_,_>(item1=data.earning,item2=data.losses)
groupValBy t data.store into group

也许有人可以对我在这里缺少的内容提出建议.

Maybe someone can offer a suggestion as to what I'm missing here.

结论:

看起来像使用let来对结果求和，然后再进行选择.不清楚为什么group.Sum()在选择行上不起作用. (注意:我上面提到的错误消息是由某些sql服务器问题引起的.)

Looks like using let to sum results before the select is the way to go. Not clear why group.Sum() won't work on the select line. (Note: the error message I noted above my have resulted from some sql server problems.)

推荐答案

尽管我必须承认，但我尚未对此进行测试-我觉得这样可能有效:

Although I have to admit, I haven't tested this - I have a feeling something like this might work:

let financials =
    query {
        for data in dbData do
        groupValBy (data.earning, data.losses) data.store into group
        let earnings = query { for (e,_) in group do sumBy e }
        let losses = query { for (_,l) in group do sumBy l }
        select (group.Key, earnings, losses) }

受到本网站上示例的启发.您可能要改用sumByNullable.

Inspired by the examples on this site. You might want to use sumByNullable instead.

这篇关于F#查询，按单列分组多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！