Thread Rating:
  • 382 Vote(s) - 2.88 Average
  • 1
  • 2
  • 3
  • 4
  • 5
RecordSet に groupby したい?
09-25-2013, 05:14 PM,
#2
RE: RecordSet に groupby したい?
集約関数の指定方法とか、もっと良い方法はないものか・・・。

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"}
            }
    }
}



Messages In This Thread
RecordSet に groupby したい? - by umemura - 09-25-2013, 04:17 PM
RE: RecordSet に groupby したい? - by umemura - 09-25-2013, 05:14 PM

Possibly Related Threads...
Thread Author Replies Views Last Post
  RecordSet.batch-events?=true 時でもRecordModifiedイベントが発行されてしまう umemura 1 2,752 12-25-2013, 11:44 AM
Last Post: umemura
  RecordSet DE RecordDataとRecord nasuB 6 8,328 08-22-2011, 11:34 AM
Last Post: nasuB
  RecordSet DE 複数の主キー nasuB 2 4,831 08-16-2011, 09:01 AM
Last Post: nasuB
  {RecordSet.commit}について thiguchi 1 4,733 08-15-2011, 01:04 PM
Last Post: fukuta
Forum Jump:


Users browsing this thread:
1 Guest(s)

MyBB SQL Error

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1017 - Can't find file: 'mybb_threadviews' (errno: 2)
Query:
INSERT INTO mybb_threadviews (tid) VALUES('970')