How to efficiently count distinct values between all date combinations

user_18045555 0
Viewed 314

I use pySpark to process website visitor datasets, where each user is assigned a unique identifier.

Visit timestamp User id
2022-01-01 10:23:44.123456 aaa
2022-01-02 11:22:44.123456 aaa
2022-01-01 11:00:44.123456 bbb
2022-01-01 12:22:44.123456 bbb
2022-01-05 13:23:44.123456 abc
2022-01-03 14:22:44.123456 ccc
2022-01-04 10:23:44.123456 ddd
2022-01-01 11:22:44.123456 eee

I am searching for an efficient way to count the number of distinct user_ids between all possible date combinations, looking for an output in the following form:

Start date End date unique visitors
2022-01-01 2022-01-01 3
2022-01-01 2022-01-02 3
2022-01-01 2022-01-03 4
2022-01-01 2022-01-04 5
2022-01-01 2022-01-05 6
2022-01-02 2022-01-02 1
2022-01-02 2022-01-03 2
2022-01-02 2022-01-04 3

Additionally, would need to store intermediary data in such a way that this can be run incrementally as new data comes in.

I can easily use the approx_count_distinct function to compute the number of distinct visitors for any given day, but that does not solve the start / end data and incremental load needs.

Thanks!

pyspark apache-spark-sql apache-spark dataframe
1 Answers

I am searching for an efficient way to count the number of distinct user ids all possible date combinations

All possible combinations of dates means you're almost doing a cross join which is not efficient. Maybe you could consider using cumulative count instead?

Using this input dataframe:

from pyspark.sql import functions as F

df = spark.createDataFrame([
    ("2022-01-01 10:23:44.123456", "aaa"), ("2022-01-02 11:22:44.123456", "aaa"),
    ("2022-01-01 11:00:44.123456", "bbb"), ("2022-01-01 12:22:44.123456", "bbb"),
    ("2022-01-05 13:23:44.123456", "abc"), ("2022-01-03 14:22:44.123456", "ccc"),
    ("2022-01-04 10:23:44.123456", "ddd"), ("2022-01-01 11:22:44.123456", "eee"),
], ["visit_timestamp", "user_id"])

df = df.withColumn("visit_date", F.to_date("visit_timestamp", "yyyy-MM-dd HH:mm:ss.SSSSSS"))

First, get distinct dates, and use self join to get all dates combinations:

dates_df = df.selectExpr("visit_date as start_date").distinct().join(
    df.selectExpr("visit_date as end_date").distinct(),
    F.col("start_date") <= F.col("end_date"),
    "left"
)

Now, join the dates_df with the original dataframe and group by to count:

result = dates_df.join(
    df,
    (F.col("visit_date") >= F.col("start_date")) & (F.col("visit_date") <= F.col("end_date")),
    "left"
).groupBy("start_date", "end_date").agg(
    F.count_distinct("user_id").alias("unique_visitors")
).orderBy("start_date", "end_date")

result.show()

#+----------+----------+---------------+
#|start_date|  end_date|unique_visitors|
#+----------+----------+---------------+
#|2022-01-01|2022-01-01|              3|
#|2022-01-01|2022-01-02|              3|
#|2022-01-01|2022-01-03|              4|
#|2022-01-01|2022-01-04|              5|
#|2022-01-01|2022-01-05|              6|
#|2022-01-02|2022-01-02|              1|
#|2022-01-02|2022-01-03|              2|
#|2022-01-02|2022-01-04|              3|
#|2022-01-02|2022-01-05|              4|
#|2022-01-03|2022-01-03|              1|
#|2022-01-03|2022-01-04|              2|
#|2022-01-03|2022-01-05|              3|
#|2022-01-04|2022-01-04|              1|
#|2022-01-04|2022-01-05|              2|
#|2022-01-05|2022-01-05|              1|
#+----------+----------+---------------+
user_13865510
Related