Odtnhj

I am working on a DataFrame looks as follows:

-------------------------------
| time | value | 
-------------------------------
| 2014-12-01 02:54:00 | 2 |
| 2014-12-01 03:54:00 | 3 |
| 2014-12-01 04:54:00 | 4 |
| 2014-12-01 05:54:00 | 5 |
| 2014-12-02 02:54:00 | 6 |
| 2014-12-02 02:54:00 | 7 |
| 2014-12-03 02:54:00 | 8 |
-------------------------------

the number of samples on each day is pretty random.

I want to get just one sample on each day, such as:

-------------------------------
| time | value | 
-------------------------------
| 2014-12-01 02:54:00 | 2 |
| 2014-12-02 02:54:00 | 6 |
| 2014-12-03 02:54:00 | 8 |
-------------------------------

I don't care about which sample I get from one day, but
I want to make sure to get one so there is no day duplication
on "time" column.

You can create a date column first and then dropDuplicates based on the date column; Example with pyspark, the syntax should be similar if you are using scala or java:

import pyspark.sql.functions as f
df.withColumn('date', f.to_date('time', 'yyyy-MM-dd HH:mm:ss')) 
 .dropDuplicates(['date']).drop('date').show()
+-------------------+-----+
| time|value|
+-------------------+-----+
|2014-12-02 02:54:00| 6|
|2014-12-03 02:54:00| 8|
|2014-12-01 02:54:00| 2|
+-------------------+-----+

You can use window functions, generate row_number by partitioning on date value and filter on row_number=1

Check this out:

val df = Seq(("2014-12-01 02:54:00","2"),("2014-12-01 03:54:00","3"),("2014-12-01 04:54:00","4"),("2014-12-01 05:54:00","5"),("2014-12-02 02:54:00","6"),("2014-12-02 02:54:00","7"),("2014-12-03 02:54:00","8"))
 .toDF("time","value")
df.withColumn("time",'time.cast("timestamp")).withColumn("value",'value.cast("int"))
df.createOrReplaceTempView("timetab")
spark.sql(
 """ with order_ts( select time, value , row_number() over(partition by date_format(time,"yyyyMMdd") order by value ) as rn from timetab)
 select time,value from order_ts where rn=1
 """).show(false)

Output:

+-------------------+-----+
|time |value|
+-------------------+-----+
|2014-12-02 02:54:00|6 |
|2014-12-01 02:54:00|2 |
|2014-12-03 02:54:00|8 |
+-------------------+-----+