by

Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    solangepaz
    @solangepaz
    Thank you :)
    solangepaz
    @solangepaz
    And how can I apply group by () and order by () in this query? I'm not able to join.stream (). Collect (groupingBy ())
    Join<Tuple5<Lineorder, Customer, Supplier, Part, Date>> join = joinComponent
                    .from(LineorderManager.IDENTIFIER)
                    .innerJoinOn(Customer.C_CUSTKEY).equal(Lineorder.LO_CUSTKEY)
                    .where(Customer.C_REGION.equal("AMERICA"))
                    .innerJoinOn(Supplier.S_SUPPKEY).equal(Lineorder.LO_SUPPKEY)
                    .where(Supplier.S_REGION.equal("AMERICA"))
                    .innerJoinOn(Part.P_PARTKEY).equal(Lineorder.LO_PARTKEY)
                    .where(Part.P_MFGR.equal("MFGR#1").or(Part.P_MFGR.equal("MFGR#2")))
                    .innerJoinOn(Date.D_DATEKEY).equal(Lineorder.LO_ORDERDATE)
                    .build(Tuples::of);
    
    
    SELECT d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit
    FROM date d, customer c, supplier s, part p, lineorder lo
    WHERE lo_custkey = c_custkey
    AND lo_suppkey = s_suppkey
    AND lo_partkey = p_partkey
    AND lo_orderdate = d_datekey
    AND c_region = 'AMERICA'
    AND s_region = 'AMERICA'
    AND (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
    GROUP BY d_year, c_nation
    ORDER BY d_year, c_nation;
    Per-Åke Minborg
    @minborg
    Take a look att the Aggregator (if you are using ‘free’) https://speedment.github.io/speedment-doc/aggregations.html#top
    Why are you unable to use collect(groupingBy()) ?
    solangepaz
    @solangepaz

    I can have this:

       JoinComponent joinComponent = app.getOrThrow(JoinComponent.class);
    
    
            Join<Tuple5<Lineorder, Customer, Supplier, Part, Date>> join = joinComponent
                    .from(LineorderManager.IDENTIFIER)
                    .innerJoinOn(Customer.C_CUSTKEY).equal(Lineorder.LO_CUSTKEY)
                    .where(Customer.C_REGION.equal("AMERICA"))
                    .innerJoinOn(Supplier.S_SUPPKEY).equal(Lineorder.LO_SUPPKEY)
                    .where(Supplier.S_REGION.equal("AMERICA"))
                    .innerJoinOn(Part.P_PARTKEY).equal(Lineorder.LO_PARTKEY)
                    .where(Part.P_MFGR.equal("MFGR#1").or(Part.P_MFGR.equal("MFGR#2")))
                    .innerJoinOn(Date.D_DATEKEY).equal(Lineorder.LO_ORDERDATE)
                    .build(Tuples::of);
    
    Map<Integer, List<Tuple5<Lineorder, Customer, Supplier, Part, Date>>> result = join.stream().collect(groupingBy(t ->t.get4().getDYear()));

    How do I apply the other group by and order by?

    solangepaz
    @solangepaz
    image.png
    I should get a result like this
    Per-Åke Minborg
    @minborg
    One idea is to use Tuples for the groupingBy(). This is an interesing question from a principal point of view. Give me some time to come up with an example.
    Per-Åke Minborg
    @minborg
    I have added an example of a similar case in the manual now: https://speedment.github.io/speedment-doc/speedment_examples.html#join-group-by-and-order-by
    Let me know if this fixes the problem @solangepaz
    solangepaz
    @solangepaz
    image.png
    I do not have the t.get option
    Per-Åke Minborg
    @minborg
    This is a problem related to Java’s type inference. Try explicitly declaring the type of t while you are experimenting.
    solangepaz
    @solangepaz
    Thank you, this worked :)
    solangepaz
    @solangepaz
    How can I convert this query to stream?
    SELECT
         l_returnflag,
         l_linestatus,
         sum (l_quantity) sum_qty,
         sum (l_extendedprice) sum_base_price,
         sum (l_extendedprice * (1 - l_discount)) sum_disc_price,
         sum (l_extendedprice * (1 - l_discount) * (1 + l_tax)) sum_charge,
         avg (l_quantity) as avg_qty,
         avg (l_extendedprice) as avg_price,
         avg (l_discount) as avg_disc,
         count (*) as count_order
    FROM
         lineitem
    ONDE
         l_shipdate <= date '1998-12-01' - interval '90' day
    GROUP BY
         l_returnflag,
         l_linestatus
    ORDER BY
         l_returnflag,
         l_linestatus;
    I have it only:
            LineitemManager lineitem = app.getOrThrow(LineitemManager.class);
            lineitem
                   .stream()
                   .filter(Lineitem.L_SHIPDATE.lessOrEqual(sqlDate))
                   .sorted(Lineitem.L_RETURNFLAG)
                   .sorted(Lineitem.L_LINESTATUS)
    Per-Åke Minborg
    @minborg
    @solangepaz Which Speedment version are you using? I would use the aggregator for this problem (requires “free” or “enterprise”)
    raghav-chamarthy
    @raghav-chamarthy
    Hey guys, we have an application that reads XML data, parses it using JAXB, does some transformation and validation and writes it to dynamo DB.
    The problem is we need to write custom code to onboard every new entity , and that makes us slow, can I use speedment to generate auto mapper code or any other open source library?? Thank you
    Per-Åke Minborg
    @minborg
    Yes you can. Speedment can generate standard SQL code and also you can plug in your own code generators. Contact me at minborg@speedment.com and I can tell you more.
    solangepaz
    @solangepaz
    @minborg, I am using the opensource version, so this query can not reproduce?
    Per-Åke Minborg
    @minborg
    You can but it is a bit tricky. You can create a stream over the line items and then apply collect(groupingBy(…)))
    You can create a custom “down stream collector” that does all the avg(), sum() etc.
    There is an open issue for creating these queries in an easier way.
    solangepaz
    @solangepaz
    @minborg Thank you. And is this query possible?
    select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from PART, SUPPLIER, PARTSUPP, NATION, REGION 
    where p_partkey = ps_partkey 
    and s_suppkey = ps_suppkey 
    and p_size = 30 
    and p_type like '%STEEL' 
    and s_nationkey = n_nationkey 
    and n_regionkey = r_regionkey 
    and r_name = 'ASIA' 
    and ps_supplycost
     = (select min(ps_supplycost) from PARTSUPP, SUPPLIER, NATION, REGION 
     where p_partkey = ps_partkey 
     and s_suppkey = ps_suppkey 
     and s_nationkey = n_nationkey 
     and n_regionkey = r_regionkey 
     and r_name = 'ASIA')
     order by s_acctbal desc, n_name, s_name, p_partkey limit 100;
    I already have this
     JoinComponent joinComponent = app.getOrThrow(JoinComponent.class);
            Join<Tuple5<Partsupp, Part, Supplier, Nation, Region>> join = joinComponent
                    .from(PartsuppManager.IDENTIFIER)
                    .where(Partsupp.PS_SUPPLYCOST.equal())
                    .innerJoinOn(Part.P_PARTKEY).equal(Partsupp.PS_PARTKEY)
                    .where(Part.P_SIZE.equal(30).and(Part.P_TYPE.endsWith("STEEL")))
                    .innerJoinOn(Supplier.S_SUPPKEY).equal(Partsupp.PS_SUPPKEY)
                    .innerJoinOn(Nation.N_NATIONKEY).equal(Supplier.S_NATIONKEY)
                    .innerJoinOn(Region.R_REGIONKEY).equal(Nation.N_REGIONKEY)
                    .where(Region.R_NAME.equal("ASIA"))
                    .build(Tuples::of);
    But I can not do the subquery
    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!