by

Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    Per-Åke Minborg
    @minborg
    I suggest that you do the sub query first and store the min value in a separate variable. This vaiable is then used in a where clause.
    solangepaz
    @solangepaz

    Thank you. And how do I get this query?

    SELECT avg (l_quantity) as avg_qty
    FROM
         lineitem
    ONDE
         l_shipdate <= date '1998-12-01' - interval '90' day
    GROUP BY
         l_returnflag,
         l_linestatus;

    I already have this, but I can not return to the average:

    
            LineitemManager lineitem = app.getOrThrow(LineitemManager.class);
            Map<Tuple2<String, String>, Long> grouped = lineitem.stream().filter(Lineitem.L_SHIPDATE.lessOrEqual(sqlDate))
                    .collect(groupingBy(t->Tuples.of(t.getLReturnflag(), t.getLLinestatus()), ));
    
    
            grouped.forEach((k, v) -> {
                System.out.format("%-32s, %,d%n", k, v);
            });
    solangepaz
    @solangepaz
    I've tried this, but it can not. Because the values I want to get as average are not recognized
    Per-Åke Minborg
    @minborg
    What type is returned by getLQuantity() ?
    solangepaz
    @solangepaz
    BigDecimal
    Per-Åke Minborg
    @minborg
    Ahhh. Ok.
    Have you tried something like: Collectors.averagingDouble(t -> t.getLQuantity().doubleValue())
    solangepaz
    @solangepaz
    It worked, thank you :)
    Per-Åke Minborg
    @minborg
    Great!
    solangepaz
    @solangepaz
    Another question, is this part possible to implement?
    sum (case
             when o_orderpriority = '1-URGENT'
                 OR o_orderpriority = '2-HIGH'
                 then 1
             else 0
         end) the high_line_count,
         sum (case
             when o_orderpriority <> '1-URGENT'
                 AND o_orderpriority <> '2-HIGH'
                 then 1
             else 0
         end) AS low_line_count
    Per-Åke Minborg
    @minborg
    They appear to be each others' inverse logic.
    In this case I would use a .collect(partitionBy(o -> o.getOrderPriority.equals(“1-URGENT”) ||o.getOrderPriority.equals(“2-HIGH”), counting()) statement
    solangepaz
    @solangepaz

    This does not return a correct result. The query would be this:

    SELECT
        l_shipmode,
        sum (case
            when o_orderpriority = '1-URGENT'
                OR o_orderpriority = '2-HIGH'
                then 1
            else 0
        end) the high_line_count,
        sum (case
            when o_orderpriority <> '1-URGENT'
                AND o_orderpriority <> '2-HIGH'
                then 1
            else 0
        end) AS low_line_count
    FROM
        orders
        lineitem
    ONDE
        o_orderkey = l_orderkey
        AND l_shipmode in ('MAIL', 'SHIP')
        AND l_receiptdate> = date '1994-01-01'
        AND l_receiptdate <date '1994-01-01' + interval '1' year
    GROUP BY
        l_shipmode

    And I have this:

    Join <Tuple2 <Orders, Lineitem >> joinFirst = joinComponent
                    .from (OrdersManager.IDENTIFIER)
                    .innerJoinOn (Lineitem.L_ORDERKEY) .equal (Orders.O_ORDERKEY)
                    .where (Lineitem.L_SHIPMODE.in ("MAIL", "SHIP"))
                    .where (Lineitem.L_RECEIPTDATE.greaterOrEqual (sqlDate) .and (Lineitem.L_RECEIPTDATE.lessThan (sqlDate2)))
                    .build (Tuples :: of);
    
    
            Map <Boolean, Long> grouped = joinFirst.stream ()
                    .collect (Collectors.partitioningBy (t> t.get0)) getOOrderpriority (). equals ("1-URGENT") || t.get0.) getOOrderpriority (). equals ("2-HIGH"), counting ()));
    
    
            grouped.forEach ((k, v) -> {
                System.out.format ("% - 32s,%, d% n", k, v);
            });
    Per-Åke Minborg
    @minborg
    Hard to spot the problem. The collect line seams to mis some characters?
    You only get the high_line_count. How does it differ?
    solangepaz
    @solangepaz

    I do not know, but for example, this returns the same result:

      Map<Boolean, Long> grouped= joinFirst.stream()
                    .collect(partitioningBy(t->t.get0().getOOrderpriority().equals("1-URGENT") || t.get0().getOOrderpriority().equals("2-HIGH"), counting()));
    Map <Boolean, Long> grouped = joinFirst.stream ()
                     .collect (partitioningBy (t> t.get0)) getOOrderpriority (). equals ("1-URGENT"), counting ()));

    and I guess that should make it different

    solangepaz
    @solangepaz
    It is counting all the rows and not through t->t.get0().getOOrderpriority().equals("2-HIGH")
    Per-Åke Minborg
    @minborg
    You should get a Map with 2 entries (True and False) that counts of all that matches the predicate and the one that does not.
    solangepaz
    @solangepaz

    Now i have this:

            Map<String ,Map<Boolean,Long>> grouped= joinFirst.stream()
                    .collect(Collectors.groupingBy(t->t.get1().getLShipmode(),
                            partitioningBy(t-> t.get0().getOOrderpriority().equals("1-URGENT") || t.get0().getOOrderpriority().equals("2-HIGH") , counting())));

    And the result is this:

    MAIL       false 13209
    MAIL       true 0
    SHIP       false 13224
    SHIP       true 0

    But it should be:

    MAIL       false 0
    MAIL       true 5376
    SHIP       false 0
    SHIP       true 5346
    Per-Åke Minborg
    @minborg
    ok. Make sure that your predicate in the partitioningBy clause really works by manually printing out the result for some items.
    solangepaz
    @solangepaz
    I think the error is as I'm doing group by, because the values are correct, that is, the total of true is correct, but it is not divided by "MAIL" and "SHIP"
    Maarten Winkels
    @mwinkels_gitlab
    Hi, I'm trying to run the "speedment:tool" goal for a SQLite database, but I get this exception:
    Caused by: java.util.NoSuchElementException: No value present
    at java.util.Optional.get(Optional.java:135)
    at com.speedment.runtime.connector.sqlite.internal.SqliteMetadataHandler.lambda$null$23(SqliteMetadataHandler.java:358)
    Can this be caused by a 'TEXT' type foreign key?
    Thanks!
    Emil Forslund
    @Pyknic
    @mwinkels_gitlab Hm, I think the exception indicates that the database has a primary key (not a foreign key) but the connector can't find the associated column. It could indicate a bug. Do you know which table it is, and if so, what primary keys does it have?
    solangepaz
    @solangepaz
    Hi, I have a question. Is speedment supposed to return different results for the same query?
    For example, I ran a query three times and those three results are different.
    Per-Åke Minborg
    @minborg
    @solangepaz Hi @solangepaz . Strictly speaking, the contract for a Manager’s stream method states that the order is unspecified:
         *The order in which elements are returned when the stream is eventually
         * consumed <em>is unspecified</em>. The order may even change from one
         * invocation to another. Thus, it is an error to assume any particular
         * element order even though is might appear, for some stream sources, that
         * there is a de-facto order.
         * <p>
         * If a deterministic order is required, then make sure to invoke the
         * {@link Stream#sorted(java.util.Comparator)} method on the {@link Stream}
         * returned.
         * <p>
    The reason is that different databases have different orders and order guarantees. Add a sorted() operation if you need a deterministic order (this will cost performance though).
    solangepaz
    @solangepaz
    This message was deleted

    But the results are very different. For example, this query in Postgresql:

    select
        l_returnflag,
        l_linestatus,
        sum(l_extendedprice) as sum_charge,
        avg(l_tax)
    from
        lineitem
    where
        l_shipdate <= date '1998-12-01'
    group by
        l_returnflag,
        l_linestatus
    order by
        l_returnflag,
        l_linestatus;

    It has the following output:
    image.png

    With the speedment the output is this:

    A F 1,20E+10 0,039955076
    N F 3,23E+08 0,040103375
    N O 1,91E+10 0,040013735
    R F 1,20E+10 0,039975758

    Per-Åke Minborg
    @minborg
    Average values looks good but not the sum(). Do you have the Java code?
    solangepaz
    @solangepaz
    Yes, the code is this:
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_MONTH,1);
        cal.set(Calendar.MONTH,Calendar.DECEMBER);
        cal.set(Calendar.YEAR,1998);
    
         java.sql.Date sqlDate = new java.sql.Date(cal.getTimeInMillis());
         LineitemManager lineitem = app.getOrThrow(LineitemManager.class);
    
         Map<Tuple2<String, String>, AbstractMap.SimpleEntry<Double, Double>> grouped = lineitem.stream().filter(Lineitem.L_SHIPDATE.lessOrEqual(sqlDate))
                    .collect(groupingBy(t->Tuples.of(t.getLReturnflag(), t.getLLinestatus()),
                            Collectors.collectingAndThen(Collectors.toList(),
                                    list-> {double first =
                                            list
                                                    .stream()
                                                    .mapToDouble(t -> t.getLExtendedprice().get().doubleValue()).sum();
                                        double second =
                                                list
                                                        .stream()
                                                        .collect(averagingDouble(t->t.getLTax().get().doubleValue()));
    
                                        return new AbstractMap.SimpleEntry<>(first, second);})
                    ));
    
    
            grouped.forEach((key, value) -> System.out.println(key + ", " + value));
    Per-Åke Minborg
    @minborg
    Can’t spot the problem. Have you checked that the Date looks ok and filters out the right elements?
    solangepaz
    @solangepaz
    Yeah, it looks okay. Even because I have already tested on two machines with the same database. The first machine returns the result just like PostgreSQL. The second machine returns these wrong results.
    The only difference is here:.mapToDouble (t -> t.getLExtendedprice (). Get (). DoubleValue ()). Sum ();
    The first machine (where it works ok) only accepts .mapToDouble (t -> t.getLExtendedprice (). DoubleValue ()). Sum () ;. And the second machine needs get () before doubleValue ()
    Per-Åke Minborg
    @minborg
    So have you set the column l_extendedprice to nullable on one but not on the other?
    In the speedment Tool I mean.
    solangepaz
    @solangepaz
    Hi, does speedment not support an H2 database? I am using SQLite, but it is very slow.
    solangepaz
    @solangepaz
    And is it possible to use this through the mutator?
    Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:"); connection.createStatement().executeUpdate("restore from database.db");
    Per-Åke Minborg
    @minborg
    Currently, there is no support for H2. However, since we now have support for SQLite, the effort of writing an H2 driver would be much less. Anyone up to the challenge?
    @solangepaz it should be possible to execute any code within the mutator. But can’t you simply run the code before you create the application builder?
    solangepaz
    @solangepaz
    Thank you, I've already been able to do this with the mutator. However SQLite is still very slow in speedment
    Per-Åke Minborg
    @minborg
    I suspect it is SQLite that is slow and not Speedment?
    solangepaz
    @solangepaz
    I think the problem is not in SQLite. If I use SQLite in memory with jdbc for a query I get a response at 376ms. With the same query in speedment and with SQLite in memory I have a response in 2ms.
    Per-Åke Minborg
    @minborg
    @solangepaz There must be some error with the response times you gave?
    solangepaz
    @solangepaz
    Yes, I'm sorry. I changed the times. The correct one is this: 376ms with speedment and sqlite in memory; 2ms with jdbc and sqlite in memory.
    Per-Åke Minborg
    @minborg
    ok. For what query/stream?
    solangepaz
    @solangepaz
    For a very simple query. In this case I tried for select count (*) from customer;
    Per-Åke Minborg
    @minborg
    ok. As you know, there is a known issue regarding this particular query (speedment/speedment#720) and we have made some progress recently.
    However, there is more to be done. If you run the query many times, I expect the difference to be much smaller.
    solangepaz
    @solangepaz
    I got those values by executing the query 10 times and calculated the mean value. I thought the same problem did not replicate with an in-memory database.