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);
});
Collectors.averagingDouble(t -> t.getLQuantity().doubleValue())
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
.collect(partitionBy(o -> o.getOrderPriority.equals(“1-URGENT”) ||o.getOrderPriority.equals(“2-HIGH”), counting())
statement
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);
});
high_line_count
. How does it differ?
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
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
*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>
sorted()
operation if you need a deterministic order (this will cost performance though).
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;
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
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));
.mapToDouble (t -> t.getLExtendedprice (). Get (). DoubleValue ()). Sum ();
.mapToDouble (t -> t.getLExtendedprice (). DoubleValue ()). Sum () ;
. And the second machine needs get ()
before doubleValue ()