Login  Register

[Minor Issue] BETWEEN AND does work as expected

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/