feat: add script to gen exec sql

1 files changed, 119 insertions, 0 deletions

diff --git a/python/gen-sql.py b/python/gen-sql.py
new file mode 100644
index 0000000..3126e75
--- /dev/null
+++ b/python/gen-sql.py
@@ -0,0 +1,119 @@
+from datetime import datetime, timedelta
+
+
+def sort_func(item):
+    if item['type'] == 'DAY':
+        return 1
+    elif item['type'] == 'HOUR':
+        return 2
+    elif item['type'] == 'HALF-HOUR':
+        return 3
+    elif item['type'] == 'TEN-MIN':
+        return 4
+    elif item['type'] == 'FIVE-MIN':
+        return 5
+    elif item['type'] == 'MIN':
+        return 6
+    else:
+        return 7
+
+
+if __name__ == '__main__':
+    days = [
+        datetime(year=2023, month=7, day=10),
+        datetime(year=2023, month=7, day=12),
+        datetime(year=2023, month=6, day=30),
+        datetime(year=2023, month=7, day=2),
+    ]
+    hours_interval = [
+        {'type': 'HOUR', 'title': '0:00-1:00', 'start': timedelta(hours=0), 'end': timedelta(hours=1)},
+        {'type': 'HOUR', 'title': '7:00-8:00', 'start': timedelta(hours=7), 'end': timedelta(hours=8)},
+        {'type': 'HOUR', 'title': '9:30-10:30', 'start': timedelta(hours=9.5), 'end': timedelta(hours=10.5)},
+        {'type': 'HOUR', 'title': '11:00-12:00', 'start': timedelta(hours=11), 'end': timedelta(hours=12)},
+        {'type': 'HOUR', 'title': '15:00-16:00', 'start': timedelta(hours=15), 'end': timedelta(hours=16)},
+
+        # half hours
+        {'type': 'HALF-HOUR', 'title': '0:00-0:30', 'start': timedelta(hours=0), 'end': timedelta(hours=0.5)},
+        {'type': 'HALF-HOUR', 'title': '6:00-6:30', 'start': timedelta(hours=6), 'end': timedelta(hours=6.5)},
+        {'type': 'HALF-HOUR', 'title': '11:30-12:00', 'start': timedelta(hours=11.5), 'end': timedelta(hours=12)},
+        {'type': 'HALF-HOUR', 'title': '16:30-17:00', 'start': timedelta(hours=16.5), 'end': timedelta(hours=17)},
+
+    ]
+    hours = [
+        {'title': '07', 'value': timedelta(hours=7)},
+        {'title': '10:30', 'value': timedelta(hours=10.5)},
+        {'title': '11', 'value': timedelta(hours=11)},
+        {'title': '16', 'value': timedelta(hours=16)},
+    ]
+    minutes_interval = [
+        # ten minutes
+        {'type': 'TEN-MIN', 'title': '-5:00-5:00', 'start': timedelta(minutes=-5), 'end': timedelta(minutes=5)},
+        {'type': 'TEN-MIN', 'title': '15:00-25:00', 'start': timedelta(minutes=15), 'end': timedelta(minutes=25)},
+        {'type': 'TEN-MIN', 'title': '30:00-40:00', 'start': timedelta(minutes=30), 'end': timedelta(minutes=40)},
+        {'type': 'TEN-MIN', 'title': '45:00-55:00', 'start': timedelta(minutes=45), 'end': timedelta(minutes=55)},
+
+        # five minutes
+        {'type': 'FIVE-MIN', 'title': '0:00-5:00', 'start': timedelta(minutes=0), 'end': timedelta(minutes=5)},
+        {'type': 'FIVE-MIN', 'title': '27:00-32:00', 'start': timedelta(minutes=27), 'end': timedelta(minutes=32)},
+        {'type': 'FIVE-MIN', 'title': '42:00-47:00', 'start': timedelta(minutes=42), 'end': timedelta(minutes=47)},
+        {'type': 'FIVE-MIN', 'title': '50:00-55:00', 'start': timedelta(minutes=50), 'end': timedelta(minutes=55)},
+
+        # minute
+        {'type': 'MIN', 'title': '0:00-1:00', 'start': timedelta(minutes=0), 'end': timedelta(minutes=1)},
+        {'type': 'MIN', 'title': '15:00-16:00', 'start': timedelta(minutes=15), 'end': timedelta(minutes=16)},
+        {'type': 'MIN', 'title': '29:00-30:00', 'start': timedelta(minutes=29), 'end': timedelta(minutes=30)},
+        {'type': 'MIN', 'title': '40:00-41:00', 'start': timedelta(minutes=40), 'end': timedelta(minutes=41)},
+    ]
+    list = []
+    for day in days:
+        list.append({'type': 'DAY', 'title': day.strftime("%Y-%m-%d"), 'start': day.strftime("%Y-%m-%d %H:%M:%S"),
+                     'end': (day + timedelta(days=1)).strftime("%Y-%m-%d %H:%M:%S")})
+        # print("DAY,{title},'{start},'{end}".format(title=day.strftime("%Y-%m-%d"), start=day,
+        #                                            end=day + timedelta(days=1)))
+
+        # hour
+        for hi in hours_interval:
+            start = day + hi['start']
+            end = day + hi['end']
+            list.append(
+                {'type': hi['type'], 'title': '{} {}'.format(day.strftime("%Y-%m-%d"), hi['title']),
+                 'start': start.strftime("%Y-%m-%d %H:%M:%S"),
+                 'end': end.strftime("%Y-%m-%d %H:%M:%S")}
+            )
+            # print("{type},{title},'{start},'{end}".format(type=hi['type'],
+            #                                               title='{} {}'.format(day.strftime("%Y-%m-%d"), hi['title']),
+            #                                               start=start,
+            #                                               end=end))
+        for hour in hours:
+            now = day + hour['value']
+            for mi in minutes_interval:
+                start = now + mi['start']
+                end = now + mi['end']
+                list.append({'type': mi['type'], 'title': '{} [{}] {}'.format(now.strftime("%Y-%m-%d"),
+                                                                              hour['title'], mi['title']),
+                             'start': start.strftime("%Y-%m-%d %H:%M:%S"),
+                             'end': end.strftime("%Y-%m-%d %H:%M:%S")})
+                # print("{type},{title},'{start},'{end}".format(type=mi['type'],
+                #                                               title='{} [{}] {}'.format(now.strftime("%Y-%m-%d"),
+                #                                                                         hour['title'], mi['title']),
+                #                                               start=start,
+                #                                               end=end))
+
+    result = sorted(list, key=sort_func)
+
+    sql = """
+    SELECT
+        '"{type}"'                                      AS tpe,
+        '"{title}"'                                     AS datetime,
+        '"{start}"'                                     AS start_time,
+        '"{end}"'                                       AS end_time,
+        count(DISTINCT http_domain)                     AS unique_domain_size,
+        count(DISTINCT http_host, ssl_sni, quic_sni)    AS unique_fqdn_size
+    FROM session_record
+    WHERE common_recv_time >= toDateTime('{start}', 'Asia/Shanghai')
+      AND common_recv_time < toDateTime('{end}', 'Asia/Shanghai')"""
+
+    sqls = '\n   UNION ALL '.join([sql.format(type=ret['type'], title=ret['title'],
+                                              start=ret['start'], end=ret['end']) for ret in result])
+
+    print(sqls)