7 Replies Latest reply: Feb 7, 2013 4:55 PM by 989856 RSS

    Min / Max in Subquery

    989856
      I'm currently doing tests on Oracle, Postgres and MySQL.
      However Oracle returns me "ORA-00934: group function is not allowed here" the same query works with the others.

      The idea is to return First,Min,Max,Last prices for a particular day on a particular ID.
      SELECT
        quotes.id,
        min(quotes.received_time) as opening_time,
        (SELECT ask_price FROM "price_quotes" WHERE id=quotes.id AND received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND received_time=min(quotes.received_time) ) AS opening_price,
        (SELECT received_time FROM "price_quotes" WHERE received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND id=quotes.id and ask_price=min(quotes.ask_price) LIMIT 1) as min_price_time,
        min(quotes.ask_price) as min_price,
        max(quotes.received_time) as closing_time, 
        (SELECT ask_price FROM "price_quotes" WHERE id=quotes.id AND received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND received_time=max(quotes.received_time)) as closing_price,
        (SELECT received_time FROM "price_quotes" WHERE received_time >=timestamp'2012-12-10 00:00:00' AND received_time < timestamp'2012-12-11 00:00:00' AND id=quotes.id and ask_price=max(quotes.ask_price) LIMIT 1) as max_price_time,
        max(quotes.ask_price) as max_price
      FROM
        "price_quotes" quotes
      WHERE
        quotes.received_time >= timestamp'2012-12-10 00:00:00' and quotes.received_time < timestamp'2012-12-11 00:00:00'
        AND quotes.id = 668792
      GROUP BY quotes.id ORDER BY quotes.id ;
      I've tried using HAVING and moving the query to the WHERE but no luck.


      Here is the Create Table:
      CREATE TABLE "price_quotes"
      (
         id number(8),
         received_time timestamp, 
         mid_price float, 
         bid_price float,
         ask_price float,
         mid_yield float,
         bid_yield float,
         ask_yield float,
         mid_spread float,
         bid_spread float,
         ask_spread float,
         product_id number(8), 
         product_yield float,
         PRIMARY KEY ( id , received_time )
      );
      Data Sample:
      INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 08:00:00', 103, 120, 110, 7, 8, 9, 2.100, 3.050, 4.28999, 29, 1.050);
      INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 10:00:00', 99, 98, 100, 4, 2, 3, 0.100, 0.050, 0.28999, 24, 0.050);
      INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 16:00:00', 100, 99, 101, 5, 3, 4, 0.200, 0.100, 0.29999, 25, 0.100);
      INSERT INTO "price_quotes" VALUES ( 668792, timestamp'2012-12-10 17:00:00', 10, 9, 11, 1, 2, 3, 0.210, 0.330, 0.99, 15, 1.100);
      Oracle : 11.2.0.1.0

      Edited by: 986853 on Feb 7, 2013 12:23 PM
        • 1. Re: Min / Max in Subquery
          AlbertoFaenza
          Hi,

          welcome to the forum.

          Please read SQL and PL/SQL FAQ

          Additionally when you put some code or output please enclose it between two lines starting with {noformat}
          {noformat}
          
          i.e.:
          {noformat}
          {noformat}
          SELECT ...
          {noformat}
          {noformat}
          
          Your statement is not working because with group by you need to use aggregate functions (i.e. MIN, MAX, etc) with the exception of column included in GROUP BY clause. The subqueries you are using are not seen as aggregate functions.
          
          Please post sample data (CREATE TABLE and INSERT statements) as specified in the FAQ.
          
          Regards.
          Al
          
          Edited by: Alberto Faenza on Feb 7, 2013 8:46 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
          • 2. Re: Min / Max in Subquery
            Frank Kulash
            Hi,

            Welcome to the forum!

            No, you can't do that in Oracle. You can have a scalar sub-query that's correlated by a GROUP BY expression, such as
            WHERE     id             = quotes.id 
            but you can't correlate by an aggregate:
            AND     received_time  = MIN (quotes.received_time)   -- *** Error in Oracle ***
            In Oracle, a better way to get the price that is related to the earlist received_time is to use the FIRST fucntion:
            SELECT    id
            ,       MIN (ask_price) KEEP (DENSE_RANK FIRST ORDER BY received_time)
                             AS opening_price
            ...
            I don't know if your other products support that or not.
            If not, you can always GROUP BY in a sub-query, and do the scalar sub-queries in a super-query.
            You can also avoid the scalar sub-queries by using analytic functions.

            In the example above, MIN (ask_price) means that, in case of a tie (2 or more rows having the same earliest received_time), then the lowest ask_price from those rows will be returned.

             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}
            • 3. Re: Min / Max in Subquery
              989856
              Thank you for the info, I've edited and corrected the post.
              Still trying to figure it out.
              • 4. Re: Min / Max in Subquery
                Frank Kulash
                Hi,
                986853 wrote:
                Thank you for the info, I've edited and corrected the post.
                Thanks.
                Don't forget to post the results you want from that sample data.
                Here is the Create Table:
                CREATE TABLE "price_quotes"
                ( 
                id number(8),
                received_time timestamp, ...
                In Oracle, table names in double-quotes are a huge pain.
                Also, if you don't need to record fractions of a second, use DATEs instead of TIMESTAMPs. They're more efficient, and there are a lot more built-in functions for dealing with DATEs.
                Of course, if you're trying to minimize differences between databases, that may influence your decision.

                Are these the results you want from the given sample data?
                `       OPENING    OPENING  MIN_ CLOSING    CLOSING MAX_PRICE   MAX_
                     ID _TIME       _PRICE PRICE _TIME       _PRICE _TIME      PRICE
                ------- ---------- ------- ----- ---------- ------- ---------- -----
                 668792 10-DEC-12      110    11 10-DEC-12       11 10-DEC-12    110
                        08.00.00.0               05.00.00.0         08.00.00.0
                        00000 AM                 00000 PM           00000 AM
                Here's one way to get them, using the aggregate FIRST and LAST fucntions:
                SELECT       id
                ,       MIN (received_time)     AS opening_time
                ,       MIN (ask_price)
                           KEEP (DENSE_RANK FIRST ORDER BY received_time)
                                                AS opening_price
                ,         MIN (ask_price)     AS min_price
                ,       MAX (received_time)     AS closing_time
                ,       MIN (ask_price)
                           KEEP (DENSE_RANK LAST  ORDER BY received_time)
                                                AS closing_price
                ,       MIN (received_time)
                           KEEP (DENSE_RANK LAST  ORDER BY ask_price)
                                                AS max_price_time
                ,       MAX (ask_price)     AS max_price
                FROM       price_quotes
                WHERE       received_time     >= TIMESTAMP '2012-12-10 00:00:00' 
                AND       received_time <  TIMESTAMP '2012-12-11 00:00:00'
                -- AND        id           = 668792
                GROUP BY  id 
                ORDER BY  id 
                ;
                • 5. Re: Min / Max in Subquery
                  989856
                  Frank,
                  I really appreciate your help and the other guys too.


                  Here are the results of running it on different database servers:

                  Oracle = First run took 54 Seconds, extra runs took 54 seconds.
                  MySQL = First run 151 seconds , extra runs took 151 seconds
                  Postgres = First run took 449 seonds, extra runs took 40 seconds.


                  Thanks
                  Charles
                  • 6. Re: Min / Max in Subquery
                    Frank Kulash
                    Hi, Charles,

                    I'm glad you got it working.

                    Are you saying that the same query I posted (using FIRST and LAST) ran on MySQL and Postgres? I don't use those products, but I'm curious about how widely used that feature is.
                    • 7. Re: Min / Max in Subquery
                      989856
                      For mySQL and Postgres the query had to be modified, it's similar to my first post message.
                      I've benchmarked it to get an idea of performance on handling a huge dataset on partitioned tables. About 160GB of data.