+- Curl Global Community (https://communities.curl.com)
+-- Forum: Discussions (https://communities.curl.com/forumdisplay.php?fid=1)
+--- Forum: General Curl questions (https://communities.curl.com/forumdisplay.php?fid=2)
+--- Thread: RecordSet に groupby したい？ (/showthread.php?tid=970)
RecordSet に groupby したい？ - umemura - 09-25-2013
ReocrdSet に対して SQL でいうところの groupby をした結果を取得するには、
どんな実装方法があるのでしょうか。
拡張ライブラリにあればうれしいなと思ったのですが、
なさそうでした。
RE: RecordSet に groupby したい？ - umemura - 09-25-2013
集約関数の指定方法とか、もっと良い方法はないものか・・・。
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"}
}
}
}