1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
| import com.starrocks.udf.Udf;
import com.starrocks.udf.annotation.UdfDescription;
import com.starrocks.udf.annotation.UdfParameter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.TimeZone;
import java.util.logging.Level;
import java.util.logging.Logger;
@UdfDescription(name = "calculate_previous_workday",
description = "Calculate the date 5 workdays before the given date considering holidays and weekends")
public class CalculatePreviousWorkday implements Udf {
private static final Logger logger = Logger.getLogger(CalculatePreviousWorkday.class.getName());
private static final Map<String, Boolean> chinaHolidays = new HashMap<>();
private static final Map<String, Boolean> europeHolidays = new HashMap<>();
private static final Map<String, Boolean> workdays = new HashMap<>();
private static final String JDBC_URL = "jdbc:mysql://your_starrocks_host:port/db_name";
private static final String JDBC_USER = "user";
private static final String JDBC_PASSWORD = "password";
// 静态块用于初始化数据
static {
loadCalendarData();
}
private static void loadCalendarData() {
logger.info("Loading calendar data into memory...");
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
String query = "SELECT date, is_workday, is_china_holiday, is_europe_holiday FROM Calendar";
try (PreparedStatement stmt = conn.prepareStatement(query);
ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
String date = rs.getString("date");
workdays.put(date, rs.getBoolean("is_workday"));
chinaHolidays.put(date, rs.getBoolean("is_china_holiday"));
europeHolidays.put(date, rs.getBoolean("is_europe_holiday"));
}
logger.info("Calendar data loaded successfully.");
}
} catch (Exception e) {
logger.log(Level.SEVERE, "Failed to load calendar data: ", e);
// 处理异常，可能需要重试机制或备用数据加载方式
}
}
public String evaluate(@UdfParameter(name = "transaction_date") String transactionDate,
@UdfParameter(name = "timezone") String timezone,
@UdfParameter(name = "region") String region) {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
sdf.setTimeZone(TimeZone.getTimeZone(timezone));
Date date = sdf.parse(transactionDate);
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
int workdaysCount = 0;
while (workdaysCount < 5) {
calendar.add(Calendar.DAY_OF_MONTH, -1);
String currentDateStr = sdf.format(calendar.getTime());
if (isWorkday(currentDateStr, region)) {
workdaysCount++;
}
}
return sdf.format(calendar.getTime());
} catch (Exception e) {
logger.log(Level.SEVERE, "Error calculating previous workday: ", e);
return null;
}
}
private boolean isWorkday(String date, String region) {
Boolean isWorkday = workdays.getOrDefault(date, false);
Boolean isHoliday = region.equals("China") ? chinaHolidays.getOrDefault(date, false)
: europeHolidays.getOrDefault(date, false);
return isWorkday && !isHoliday;
}
// Method to update calendar data if needed
public static void updateCalendarData() {
synchronized (CalculatePreviousWorkday.class) {
logger.info("Updating calendar data...");
chinaHolidays.clear();
europeHolidays.clear();
workdays.clear();
loadCalendarData();
logger.info("Calendar data updated.");
}
}
}
|
