Huib
08/10/2024, 6:21 PMSelect count(*) from tbl where time_col >= current_timestamp - interval ‘5’ minute
But this never removes entries that no longer fit the window, instead only increasing the count when new data comes in.
I guess this is because the filter is evaluated once, then left alone.
Is something like this possible, or do I need to fall back to windows and possibly the datastream api?D. Draco O'Brien
08/11/2024, 4:07 AMD. Draco O'Brien
08/11/2024, 4:09 AMSELECT
HOP_START(time_col, INTERVAL '1' MINUTE, INTERVAL '5' MINUTE) as window_start,
COUNT(*)
FROM tbl
GROUP BY HOP(time_col, INTERVAL '1' MINUTE, INTERVAL '5' MINUTE)
D. Draco O'Brien
08/11/2024, 4:11 AMHuib
08/11/2024, 7:22 AMD. Draco O'Brien
08/11/2024, 7:31 AMHuib
08/11/2024, 9:14 AMD. Draco O'Brien
08/11/2024, 9:27 AMfrom pyflink.dataset import ExecutionEnvironment
from pyflink.table import TableConfig, DataTypes, BatchTableEnvironment
from pyflink.table.descriptors import Schema, OldCsv, FileSystem
from pyflink.table.window import Tumble
from pyflink.table.expressions import lit, col
env = ExecutionEnvironment.get_execution_environment()
t_config = TableConfig()
t_env = BatchTableEnvironment.create(env, t_config)
# assuming you read from a CSV file or another source
t_env.connect(FileSystem().path("path_to_your_csv"))
.with_format(OldCsv()
.field("time_col", DataTypes.TIMESTAMP(3))
# Add other fields as needed
)
.with_schema(Schema()
.field("time_col", DataTypes.TIMESTAMP(3))
# Define other fields
)
.register_table_source("mySource")
t_env.create_temporary_view("myTable", t_env.from_path("mySource"))
# define a sliding window of 1 second every 1 second, and filter for last 5 minutes
result = t_env.sql_query("""
SELECT
TUMBLE_START(time_col, INTERVAL '1' SECOND) as window_start,
COUNT(*)
FROM myTable
WHERE time_col >= NOW() - INTERVAL '5' MINUTE
GROUP BY TUMBLE(time_col, INTERVAL '1' SECOND)
""")
t_env.to_append_stream(result, schema).print()
t_env.execute("SlidingWindowCount")
D. Draco O'Brien
08/11/2024, 9:28 AMD. Draco O'Brien
08/11/2024, 10:51 AMHuib
08/11/2024, 11:21 AMD. Draco O'Brien
08/11/2024, 11:35 AM