Posted by
aaron on
Sep 27, 2018; 8:54am
URL: http://apache-carbondata-dev-mailing-list-archive.168.s1.nabble.com/Minor-Issue-BETWEEN-AND-does-work-as-expected-tp63983.html
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/