RecordSet に groupby したい？

[umemura]

ReocrdSet に対して SQL でいうところの groupby をした結果を取得するには、
どんな実装方法があるのでしょうか。

拡張ライブラリにあればうれしいなと思ったのですが、
なさそうでした。

[umemura]

集約関数の指定方法とか、もっと良い方法はないものか・・・。

Code:

{let people:RecordSet =
    {RecordSet
        {RecordFields
            {RecordField "First", domain = String},
            {RecordField "Last", domain = String},
            {RecordField "Age", domain = int}
        },
        {RecordData First = "John", Last = "Smith", Age = 25},
        {RecordData First = "Jane", Last = "Smith", Age = 49},
        {RecordData First = "John", Last = "Smith", Age = 30},
        {RecordData First = "Jane", Last = "Jones", Age = 22},
        {RecordData First = "Jane", Last = "Jones", Age = 12},
        {RecordData First = "Jane", Last = "Smith", Age = 29},
        {RecordData First = "Jane", Last = "Jones", Age = 28}
    }
}
{value
    {RecordGrid
        record-source = people,
        height = 6cm
    }
}


||レコードセットに対してgroupby をかけて、新しいレコードセットをつくる

{define-proc public {group-by
                        rs:RecordSet,
                        group-by-fields:StringArray,
                        sum-fields:#StringArray = null
                    }:RecordSet

    def rf-ary = {{Array-of RecordField}}

    {for rf-name in group-by-fields do
        def (rf:RecordField, found?:bool) =
            {rs.fields.get-if-exists rf-name}
        {if found? then
            {rf-ary.append rf}
        }
    }

    {if-non-null sum-fields then
        {for sum-rf-name in sum-fields do
            {rf-ary.append
                {RecordField "sum(" & sum-rf-name & ")",
                    nullable? = true,
                    domain = int,
                    default-value = 0
                }
            }
        }
    }

    def rfs = {RecordFields {splice rf-ary}}
    def grouped-rs = {RecordSet rfs}
    def extracted-records = {{Array-of Record}}

    {for target-record in rs do

        def selected-records =
            {rs.select
                filter =
                    {RecordFilter
                        {proc {r:Record}:bool
                            {if {extracted-records.find r} > -1 then
                                {return false}
                            }
                            let matched?:bool = true
                            {for rf-name in group-by-fields do
                                {if target-record[rf-name] != r[rf-name] then
                                    set matched? = false
                                    {break}
                                }
                            }
                            {return matched?}
                        }
                    }
            }

        {extracted-records.concat selected-records}

        {if selected-records.size > 0 then

            def new-r = {grouped-rs.new-record}

            {for rf-name in group-by-fields do
                set new-r[rf-name] = selected-records[0][rf-name]
            }

            {grouped-rs.append new-r}

            {for r in selected-records do
                {if-non-null sum-fields then
                    {for sum-rf-name in sum-fields do
                        set new-r[ "sum(" & sum-rf-name & ")"] =
                            (new-r[ "sum(" & sum-rf-name & ")"] asa int) +
                        (r[sum-rf-name] asa int)
                    }
                }
            }
        }
    }
    {return grouped-rs}
}

{def grouped-rg = {RecordGrid  height = 3cm}}

{value grouped-rg}

{CommandButton
    label = "名前でGROUPBY",
    {on Action do
        set grouped-rg.record-source =
            {group-by
                people,
                {StringArray "First", "Last"},
                sum-fields = {StringArray "Age"}
            }
    }
}