Kirill Simonov avatar Kirill Simonov committed 111355e

Added aggregates `sum` and `avg`.

Comments (0)

Files changed (3)

src/htsql/tr/fn/function.py

         MaxWrapperPhrase, "%(expression)s")
 
 
+class SumFunction(ProperFunction):
+
+    named('sum')
+
+    parameters = [
+            Parameter('expression'),
+    ]
+
+    def correlate(self, expression):
+        Implementation = Sum.realize((type(expression.domain),))
+        function = Implementation(expression, self.state, self.syntax)
+        yield function()
+
+
+class Sum(Adapter):
+
+    adapts(Domain)
+
+    def __init__(self, expression, state, syntax):
+        self.expression = expression
+        self.state = state
+        self.syntax = syntax
+
+    def __call__(self):
+        expression = self.expression
+        return SumBinding(expression.domain, self.syntax,
+                          base=self.state.base, expression=expression)
+
+
+class SumInteger(Sum):
+
+    adapts(IntegerDomain)
+
+
+class SumDecimal(Sum):
+
+    adapts(DecimalDomain)
+
+
+class SumFloat(Sum):
+
+    adapts(FloatDomain)
+
+
+SumBinding = GenericBinding.factory(SumFunction)
+SumExpression = GenericExpression.factory(SumFunction)
+SumWrapperExpression = GenericExpression.factory(SumFunction)
+SumPhrase = GenericPhrase.factory(SumFunction)
+SumWrapperPhrase = GenericPhrase.factory(SumFunction)
+
+
+EncodeSum = GenericAggregateEncode.factory(SumFunction,
+        SumBinding, SumExpression, SumWrapperExpression)
+EvaluateSum = GenericEvaluate.factory(SumFunction,
+        SumExpression, SumPhrase)
+EvaluateSumWrapper = GenericEvaluate.factory(SumFunction,
+        SumWrapperExpression, SumWrapperPhrase)
+SerializeSum = GenericSerialize.factory(SumFunction,
+        SumPhrase, "SUM(%(expression)s)")
+SerializeSumWrapper = GenericSerialize.factory(SumFunction,
+        SumWrapperPhrase, "%(expression)s")
+
+
+class AvgFunction(ProperFunction):
+
+    named('avg')
+
+    parameters = [
+            Parameter('expression'),
+    ]
+
+    def correlate(self, expression):
+        Implementation = Avg.realize((type(expression.domain),))
+        function = Implementation(expression, self.state, self.syntax)
+        yield function()
+
+
+class Avg(Adapter):
+
+    adapts(Domain)
+
+    domain = None
+
+    def __init__(self, expression, state, syntax):
+        self.expression = expression
+        self.state = state
+        self.syntax = syntax
+
+    def __call__(self):
+        expression = CastBinding(self.expression, self.domain,
+                                 self.expression.syntax)
+        return AvgBinding(expression.domain, self.syntax,
+                          base=self.state.base, expression=expression)
+
+
+class AvgDecimal(Avg):
+
+    adapts_many(IntegerDomain, DecimalDomain)
+
+    domain = DecimalDomain()
+
+
+class AvgFloat(Avg):
+
+    adapts(FloatDomain)
+
+    domain = FloatDomain()
+
+
+AvgBinding = GenericBinding.factory(AvgFunction)
+AvgExpression = GenericExpression.factory(AvgFunction)
+AvgWrapperExpression = GenericExpression.factory(AvgFunction)
+AvgPhrase = GenericPhrase.factory(AvgFunction)
+AvgWrapperPhrase = GenericPhrase.factory(AvgFunction)
+
+
+EncodeAvg = GenericAggregateEncode.factory(AvgFunction,
+        AvgBinding, AvgExpression, AvgWrapperExpression)
+EvaluateAvg = GenericEvaluate.factory(AvgFunction,
+        AvgExpression, AvgPhrase)
+EvaluateAvgWrapper = GenericEvaluate.factory(AvgFunction,
+        AvgWrapperExpression, AvgWrapperPhrase)
+SerializeAvg = GenericSerialize.factory(AvgFunction,
+        AvgPhrase, "AVG(%(expression)s)")
+SerializeAvgWrapper = GenericSerialize.factory(AvgFunction,
+        AvgWrapperPhrase, "%(expression)s")
+
+
 def call(syntax, state, base=None):
     if base is not None:
         state.push_base(base)

test/input/pgsql.yaml

     - uri: /department?exists(course.credits=5)
     - uri: /department?every(course.credits=5)
     - uri: /department{code,min(course.credits),max(course.credits)}
+    - uri: /department{code,avg(course.credits),
+                            sum(course.credits)/count(course.credits)}
     - uri: /department?exists(course)
     - uri: /school?!exists(department)
     - uri: /school{*,count(department)}

test/output/pgsql.yaml

          ----
          /department{code,min(course.credits),max(course.credits)}
          SELECT "department"."code", "course_1"."!", "course_2"."!" FROM "ad"."department" AS "department" LEFT OUTER JOIN (SELECT MIN("course"."credits") AS "!", "course"."department" FROM "ad"."course" AS "course" GROUP BY 2) AS "course_1" ON (("department"."code" = "course_1"."department")) LEFT OUTER JOIN (SELECT MAX("course"."credits") AS "!", "course"."department" FROM "ad"."course" AS "course" GROUP BY 2) AS "course_2" ON (("department"."code" = "course_2"."department")) ORDER BY 1 ASC
+    - uri: /department{code,avg(course.credits), sum(course.credits)/count(course.credits)}
+      status: 200 OK
+      headers:
+      - [Content-Type, text/plain; charset=UTF-8]
+      body: |2
+         | department                                                               |
+        -+--------------------------------------------------------------------------+-
+         | code   | avg(course.credits) | sum(course.credits)/count(course.credits) |
+        -+--------+---------------------+-------------------------------------------+-
+         | acc    |  3.1666666666666667 |                        3.1666666666666667 |
+         | arthis |  3.1666666666666667 |                        3.1666666666666667 |
+         | artstd |  3.1666666666666667 |                        3.1666666666666667 |
+         | astro  |  2.2500000000000000 |                        2.2500000000000000 |
+         | be     |  4.2500000000000000 |                        4.2500000000000000 |
+         | capmrk |  3.0000000000000000 |                        3.0000000000000000 |
+         | chem   |  2.8000000000000000 |                        2.8000000000000000 |
+         | comp   |  3.1428571428571429 |                        3.1428571428571429 |
+         | corpfi |  3.0000000000000000 |                        3.0000000000000000 |
+         | edpol  |  3.0000000000000000 |                        3.0000000000000000 |
+         | ee     |  3.2000000000000000 |                        3.2000000000000000 |
+         | eng    |  2.6666666666666667 |                        2.6666666666666667 |
+         | hist   |  3.0000000000000000 |                        3.0000000000000000 |
+         | lang   |  3.0000000000000000 |                        3.0000000000000000 |
+         | me     |  3.4000000000000000 |                        3.4000000000000000 |
+         | mth    |  5.0000000000000000 |                        5.0000000000000000 |
+         | phys   |  2.8333333333333333 |                        2.8333333333333333 |
+         | pia    |                     |                                           |
+         | poli   |  3.1666666666666667 |                        3.1666666666666667 |
+         | psych  |  3.4000000000000000 |                        3.4000000000000000 |
+         | str    |                     |                                           |
+         | tched  |  3.2000000000000000 |                        3.2000000000000000 |
+         | voc    |                     |                                           |
+         | win    |                     |                                           |
+                                                                            (24 rows)
+
+         ----
+         /department{code,avg(course.credits),sum(course.credits)/count(course.credits)}
+         SELECT "department"."code", "course_1"."!", (CAST("course_2"."!" AS NUMERIC) / CAST(COALESCE("course_3"."!", 0) AS NUMERIC)) FROM "ad"."department" AS "department" LEFT OUTER JOIN (SELECT AVG(CAST("course"."credits" AS NUMERIC)) AS "!", "course"."department" FROM "ad"."course" AS "course" GROUP BY 2) AS "course_1" ON (("department"."code" = "course_1"."department")) LEFT OUTER JOIN (SELECT SUM("course"."credits") AS "!", "course"."department" FROM "ad"."course" AS "course" GROUP BY 2) AS "course_2" ON (("department"."code" = "course_2"."department")) LEFT OUTER JOIN (SELECT COUNT(NULLIF(("course"."credits" IS NOT NULL), FALSE)) AS "!", "course"."department" FROM "ad"."course" AS "course" GROUP BY 2) AS "course_3" ON (("department"."code" = "course_3"."department")) ORDER BY 1 ASC
     - uri: /department?exists(course)
       status: 200 OK
       headers:
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.