You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@carbondata.apache.org by aaron <94...@qq.com> on 2018/09/27 08:54:38 UTC
[Minor Issue] BETWEEN AND does work as expected
Hi Community,
The BETWEEN AND work as >= AND <, I guess is should be >= AND <=. My env is
spark2.2.2 + carbondata1.4.1
%Carbondata
scala> carbon.time(carbon.sql(
| s"""SELECT timeseries(date, 'DAY') as day, market_code,
device_code, country_code, category_id,
| |sum(est_free_app_download), sum(est_paid_app_download),
sum(est_revenue)
| |FROM store WHERE date BETWEEN '2016-09-01' AND '2016-09-06'
AND device_code='ios-phone' AND country_code='EE' AND category_id=100021
| |GROUP BY timeseries(date, 'DAY'), market_code, device_code,
country_code, category_id"""
| .stripMargin).show(truncate=false)
| )
+-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|day
|market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
+-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|2016-09-02 00:00:00|apple-store|ios-phone |EE |100021 |30807
|14092 |648 |
|2016-09-04 00:00:00|apple-store|ios-phone |EE |100021 |32137
|14088 |875 |
|2016-09-05 00:00:00|apple-store|ios-phone |EE |100021 |30774
|14083 |930 |
|2016-09-01 00:00:00|apple-store|ios-phone |EE |100021 |30408
|14096 |932 |
|2016-09-03 00:00:00|apple-store|ios-phone |EE |100021 |32476
|14101 |818 |
+-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
%pyspark
(
spark.read
.parquet("s3a://b2b-prod-int-data-pipeline-unified/unified/app-ss.storeint.v1/metric")
.where("date BETWEEN '2016-09-01' AND '2016-09-06' AND
device_code='ios-phone' AND country_code='EE' AND category_id=100021")
.groupBy("date", "market_code", "device_code", "country_code",
"category_id")
.agg({"est_free_app_download": "sum", "est_paid_app_download": "sum",
"est_revenue": "sum"})
.show()
)
+----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|
date|market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
+----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
|2016-09-04|apple-store| ios-phone| EE| 100021|
32137| 14088| 875|
|2016-09-06|apple-store| ios-phone| EE| 100021|
31425| 14103| 893|
|2016-09-01|apple-store| ios-phone| EE| 100021|
30408| 14096| 932|
|2016-09-05|apple-store| ios-phone| EE| 100021|
30774| 14083| 930|
|2016-09-03|apple-store| ios-phone| EE| 100021|
32476| 14101| 818|
|2016-09-02|apple-store| ios-phone| EE| 100021|
30807| 14092| 648|
+----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
Re: [Minor Issue] BETWEEN AND does work as expected
Posted by aaron <94...@qq.com>.
Cool! Thanks a lot for your effort!
--
Sent from: http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
Re: [Minor Issue] BETWEEN AND does work as expected
Posted by Kumar Vishal <ku...@gmail.com>.
Hi Aaron,
I am able to reproduce this issue. I have raised PR:
https://github.com/apache/carbondata/pull/2787
-Regards
Kumar Vishal
On Thu, Sep 27, 2018 at 2:24 PM aaron <94...@qq.com> wrote:
> Hi Community,
>
> The BETWEEN AND work as >= AND <, I guess is should be >= AND <=. My env
> is
> spark2.2.2 + carbondata1.4.1
>
> %Carbondata
>
> scala> carbon.time(carbon.sql(
> | s"""SELECT timeseries(date, 'DAY') as day, market_code,
> device_code, country_code, category_id,
> | |sum(est_free_app_download), sum(est_paid_app_download),
> sum(est_revenue)
> | |FROM store WHERE date BETWEEN '2016-09-01' AND
> '2016-09-06'
> AND device_code='ios-phone' AND country_code='EE' AND category_id=100021
> | |GROUP BY timeseries(date, 'DAY'), market_code,
> device_code,
> country_code, category_id"""
> | .stripMargin).show(truncate=false)
> | )
>
> +-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |day
>
> |market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
>
> +-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |2016-09-02 00:00:00|apple-store|ios-phone |EE |100021
> |30807
> |14092 |648 |
> |2016-09-04 00:00:00|apple-store|ios-phone |EE |100021
> |32137
> |14088 |875 |
> |2016-09-05 00:00:00|apple-store|ios-phone |EE |100021
> |30774
> |14083 |930 |
> |2016-09-01 00:00:00|apple-store|ios-phone |EE |100021
> |30408
> |14096 |932 |
> |2016-09-03 00:00:00|apple-store|ios-phone |EE |100021
> |32476
> |14101 |818 |
>
> +-------------------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
>
>
>
>
> %pyspark
>
> (
> spark.read
>
>
> .parquet("s3a://b2b-prod-int-data-pipeline-unified/unified/app-ss.storeint.v1/metric")
> .where("date BETWEEN '2016-09-01' AND '2016-09-06' AND
> device_code='ios-phone' AND country_code='EE' AND category_id=100021")
> .groupBy("date", "market_code", "device_code", "country_code",
> "category_id")
> .agg({"est_free_app_download": "sum", "est_paid_app_download": "sum",
> "est_revenue": "sum"})
> .show()
> )
>
>
> +----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |
>
> date|market_code|device_code|country_code|category_id|sum(est_free_app_download)|sum(est_paid_app_download)|sum(est_revenue)|
>
> +----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
> |2016-09-04|apple-store| ios-phone| EE| 100021|
>
> 32137| 14088| 875|
> |2016-09-06|apple-store| ios-phone| EE| 100021|
>
> 31425| 14103| 893|
> |2016-09-01|apple-store| ios-phone| EE| 100021|
>
> 30408| 14096| 932|
> |2016-09-05|apple-store| ios-phone| EE| 100021|
>
> 30774| 14083| 930|
> |2016-09-03|apple-store| ios-phone| EE| 100021|
>
> 32476| 14101| 818|
> |2016-09-02|apple-store| ios-phone| EE| 100021|
>
> 30807| 14092| 648|
>
> +----------+-----------+-----------+------------+-----------+--------------------------+--------------------------+----------------+
>
>
>
>
> --
> Sent from:
> http://apache-carbondata-dev-mailing-list-archive.1130556.n5.nabble.com/
>