423 lines
16 KiB
PL/PgSQL
423 lines
16 KiB
PL/PgSQL
-- 训练偏好分析系统数据库扩展
|
||
-- 用于支持训练偏好分析功能的表和函数
|
||
|
||
-- 用户偏好历史记录表
|
||
CREATE TABLE public.ak_user_preference_history (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
sport_type_id uuid REFERENCES public.ak_sport_types(id),
|
||
action_type VARCHAR(32) NOT NULL, -- 'created', 'updated', 'deleted', 'favorite_added', 'favorite_removed'
|
||
old_values JSONB, -- 旧值
|
||
new_values JSONB, -- 新值
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
COMMENT ON TABLE public.ak_user_preference_history IS '用户偏好历史记录表';
|
||
CREATE INDEX idx_preference_history_user_id ON public.ak_user_preference_history(user_id);
|
||
CREATE INDEX idx_preference_history_action ON public.ak_user_preference_history(action_type);
|
||
CREATE INDEX idx_preference_history_date ON public.ak_user_preference_history(created_at);
|
||
|
||
-- 目标历史记录表
|
||
CREATE TABLE public.ak_goal_progress_history (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
goal_id uuid REFERENCES public.ak_user_training_goals(id) ON DELETE CASCADE,
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
old_value FLOAT,
|
||
new_value FLOAT,
|
||
change_amount FLOAT,
|
||
change_type VARCHAR(32), -- 'increase', 'decrease', 'target_adjusted'
|
||
recorded_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
COMMENT ON TABLE public.ak_goal_progress_history IS '目标进度历史记录表';
|
||
CREATE INDEX idx_goal_progress_history_goal_id ON public.ak_goal_progress_history(goal_id);
|
||
CREATE INDEX idx_goal_progress_history_user_id ON public.ak_goal_progress_history(user_id);
|
||
CREATE INDEX idx_goal_progress_history_date ON public.ak_goal_progress_history(recorded_at);
|
||
|
||
-- 训练模式分析表
|
||
CREATE TABLE public.ak_training_patterns (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
pattern_type VARCHAR(32) NOT NULL, -- 'weekly_schedule', 'intensity_preference', 'duration_preference'
|
||
pattern_data JSONB NOT NULL,
|
||
confidence_score FLOAT DEFAULT 0.5, -- 置信度分数 0-1
|
||
detected_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
COMMENT ON TABLE public.ak_training_patterns IS '训练模式分析表';
|
||
CREATE INDEX idx_training_patterns_user_id ON public.ak_training_patterns(user_id);
|
||
CREATE INDEX idx_training_patterns_type ON public.ak_training_patterns(pattern_type);
|
||
CREATE UNIQUE INDEX idx_training_patterns_unique ON public.ak_training_patterns(user_id, pattern_type);
|
||
|
||
-- 个性化推荐记录表
|
||
CREATE TABLE public.ak_personalized_recommendations (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
recommendation_type VARCHAR(32) NOT NULL, -- 'sport', 'schedule', 'intensity', 'goal'
|
||
title VARCHAR(128) NOT NULL,
|
||
description TEXT,
|
||
recommendation_data JSONB,
|
||
reason TEXT, -- 推荐理由
|
||
priority INT DEFAULT 1, -- 优先级 1-5
|
||
status VARCHAR(16) DEFAULT 'pending', -- 'pending', 'viewed', 'applied', 'dismissed'
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
|
||
viewed_at TIMESTAMP WITH TIME ZONE,
|
||
applied_at TIMESTAMP WITH TIME ZONE
|
||
);
|
||
COMMENT ON TABLE public.ak_personalized_recommendations IS '个性化推荐记录表';
|
||
CREATE INDEX idx_recommendations_user_id ON public.ak_personalized_recommendations(user_id);
|
||
CREATE INDEX idx_recommendations_status ON public.ak_personalized_recommendations(status);
|
||
CREATE INDEX idx_recommendations_type ON public.ak_personalized_recommendations(recommendation_type);
|
||
|
||
-- 用户行为分析表
|
||
CREATE TABLE public.ak_user_behavior_analytics (
|
||
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id uuid REFERENCES public.ak_users(id) ON DELETE CASCADE,
|
||
behavior_type VARCHAR(32) NOT NULL, -- 'preference_change', 'goal_setting', 'reminder_usage', 'device_sync'
|
||
behavior_data JSONB,
|
||
session_id uuid, -- 用于关联同一会话的行为
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
|
||
);
|
||
COMMENT ON TABLE public.ak_user_behavior_analytics IS '用户行为分析表';
|
||
CREATE INDEX idx_behavior_analytics_user_id ON public.ak_user_behavior_analytics(user_id);
|
||
CREATE INDEX idx_behavior_analytics_type ON public.ak_user_behavior_analytics(behavior_type);
|
||
CREATE INDEX idx_behavior_analytics_date ON public.ak_user_behavior_analytics(created_at);
|
||
|
||
-- 创建获取用户偏好分析数据的函数
|
||
CREATE OR REPLACE FUNCTION get_user_preferences_analytics(p_user_id uuid)
|
||
RETURNS JSONB AS $$
|
||
DECLARE
|
||
result JSONB := '{}'::JSONB;
|
||
favorite_count INT;
|
||
weekly_hours FLOAT;
|
||
category_stats JSONB;
|
||
intensity_stats JSONB;
|
||
trend_data JSONB;
|
||
BEGIN
|
||
-- 获取喜爱运动数量
|
||
SELECT COUNT(*) INTO favorite_count
|
||
FROM public.ak_user_sport_preferences
|
||
WHERE user_id = p_user_id AND is_favorite = true;
|
||
|
||
-- 计算每周训练时长
|
||
SELECT COALESCE(SUM(frequency_per_week * duration_minutes), 0) / 60.0 INTO weekly_hours
|
||
FROM public.ak_user_sport_preferences
|
||
WHERE user_id = p_user_id;
|
||
|
||
-- 获取运动类型分布
|
||
SELECT jsonb_agg(
|
||
jsonb_build_object(
|
||
'category', st.category,
|
||
'count', category_count,
|
||
'percentage', ROUND((category_count::FLOAT / total_count::FLOAT) * 100, 1)
|
||
)
|
||
) INTO category_stats
|
||
FROM (
|
||
SELECT
|
||
st.category,
|
||
COUNT(*) as category_count,
|
||
(SELECT COUNT(*) FROM public.ak_user_sport_preferences WHERE user_id = p_user_id) as total_count
|
||
FROM public.ak_user_sport_preferences usp
|
||
JOIN public.ak_sport_types st ON usp.sport_type_id = st.id
|
||
WHERE usp.user_id = p_user_id
|
||
GROUP BY st.category
|
||
) category_data;
|
||
|
||
-- 获取强度分析
|
||
SELECT jsonb_agg(
|
||
jsonb_build_object(
|
||
'intensity_level', intensity_level,
|
||
'count', level_count,
|
||
'percentage', ROUND((level_count::FLOAT / total_count::FLOAT) * 100, 1)
|
||
)
|
||
) INTO intensity_stats
|
||
FROM (
|
||
SELECT
|
||
intensity_level,
|
||
COUNT(*) as level_count,
|
||
(SELECT COUNT(*) FROM public.ak_user_sport_preferences WHERE user_id = p_user_id) as total_count
|
||
FROM public.ak_user_sport_preferences
|
||
WHERE user_id = p_user_id
|
||
GROUP BY intensity_level
|
||
) intensity_data;
|
||
|
||
-- 获取趋势数据（最近30天的偏好变化）
|
||
SELECT jsonb_agg(
|
||
jsonb_build_object(
|
||
'date', date_trunc('day', created_at),
|
||
'action_type', action_type,
|
||
'count', action_count
|
||
)
|
||
) INTO trend_data
|
||
FROM (
|
||
SELECT
|
||
created_at,
|
||
action_type,
|
||
COUNT(*) as action_count
|
||
FROM public.ak_user_preference_history
|
||
WHERE user_id = p_user_id
|
||
AND created_at >= NOW() - INTERVAL '30 days'
|
||
GROUP BY date_trunc('day', created_at), action_type
|
||
ORDER BY created_at DESC
|
||
) trend_history;
|
||
|
||
-- 构建结果
|
||
result = jsonb_build_object(
|
||
'favorite_count', favorite_count,
|
||
'weekly_hours', weekly_hours,
|
||
'category_distribution', COALESCE(category_stats, '[]'::JSONB),
|
||
'intensity_analysis', COALESCE(intensity_stats, '[]'::JSONB),
|
||
'trend_data', COALESCE(trend_data, '[]'::JSONB)
|
||
);
|
||
|
||
RETURN result;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 创建生成个性化推荐的函数
|
||
CREATE OR REPLACE FUNCTION generate_personalized_recommendations(p_user_id uuid)
|
||
RETURNS VOID AS $$
|
||
DECLARE
|
||
user_preferences RECORD;
|
||
recommendation_count INT;
|
||
BEGIN
|
||
-- 清理旧的待处理推荐
|
||
DELETE FROM public.ak_personalized_recommendations
|
||
WHERE user_id = p_user_id
|
||
AND status = 'pending'
|
||
AND created_at < NOW() - INTERVAL '7 days';
|
||
|
||
-- 获取用户偏好信息
|
||
SELECT
|
||
COUNT(*) as total_preferences,
|
||
COUNT(*) FILTER (WHERE is_favorite = true) as favorite_count,
|
||
AVG(intensity_level) as avg_intensity,
|
||
SUM(frequency_per_week * duration_minutes) as weekly_minutes
|
||
INTO user_preferences
|
||
FROM public.ak_user_sport_preferences
|
||
WHERE user_id = p_user_id;
|
||
|
||
-- 检查是否需要扩展运动偏好
|
||
IF user_preferences.favorite_count < 3 THEN
|
||
INSERT INTO public.ak_personalized_recommendations (
|
||
user_id, recommendation_type, title, description, reason, priority
|
||
) VALUES (
|
||
p_user_id, 'sport', '扩展运动偏好',
|
||
'尝试添加更多喜爱的运动类型，让训练更有趣',
|
||
'当前喜爱运动少于3种', 3
|
||
);
|
||
END IF;
|
||
|
||
-- 检查是否需要增加训练时间
|
||
IF user_preferences.weekly_minutes < 180 THEN -- 少于3小时
|
||
INSERT INTO public.ak_personalized_recommendations (
|
||
user_id, recommendation_type, title, description, reason, priority
|
||
) VALUES (
|
||
p_user_id, 'schedule', '增加训练时间',
|
||
'建议每周至少进行3小时的体育锻炼',
|
||
'当前每周训练时间不足', 4
|
||
);
|
||
END IF;
|
||
|
||
-- 检查是否需要调整训练强度
|
||
IF user_preferences.avg_intensity < 2.5 THEN
|
||
INSERT INTO public.ak_personalized_recommendations (
|
||
user_id, recommendation_type, title, description, reason, priority
|
||
) VALUES (
|
||
p_user_id, 'intensity', '增加训练强度',
|
||
'适当增加高强度训练可以提高运动效果',
|
||
'当前训练强度偏低', 2
|
||
);
|
||
END IF;
|
||
|
||
-- 检查是否需要设定目标
|
||
SELECT COUNT(*) INTO recommendation_count
|
||
FROM public.ak_user_training_goals
|
||
WHERE user_id = p_user_id AND status = 'active';
|
||
|
||
IF recommendation_count = 0 THEN
|
||
INSERT INTO public.ak_personalized_recommendations (
|
||
user_id, recommendation_type, title, description, reason, priority
|
||
) VALUES (
|
||
p_user_id, 'goal', '设定训练目标',
|
||
'设定明确的训练目标有助于保持动力',
|
||
'当前没有活跃的训练目标', 5
|
||
);
|
||
END IF;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 创建记录用户行为的函数
|
||
CREATE OR REPLACE FUNCTION log_user_behavior(
|
||
p_user_id uuid,
|
||
p_behavior_type VARCHAR(32),
|
||
p_behavior_data JSONB DEFAULT NULL,
|
||
p_session_id uuid DEFAULT NULL
|
||
)
|
||
RETURNS VOID AS $$
|
||
BEGIN
|
||
INSERT INTO public.ak_user_behavior_analytics (
|
||
user_id, behavior_type, behavior_data, session_id
|
||
) VALUES (
|
||
p_user_id, p_behavior_type, p_behavior_data, p_session_id
|
||
);
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 创建更新训练模式的函数
|
||
CREATE OR REPLACE FUNCTION update_training_pattern(
|
||
p_user_id uuid,
|
||
p_pattern_type VARCHAR(32),
|
||
p_pattern_data JSONB,
|
||
p_confidence_score FLOAT DEFAULT 0.5
|
||
)
|
||
RETURNS VOID AS $$
|
||
BEGIN
|
||
INSERT INTO public.ak_training_patterns (
|
||
user_id, pattern_type, pattern_data, confidence_score
|
||
) VALUES (
|
||
p_user_id, p_pattern_type, p_pattern_data, p_confidence_score
|
||
)
|
||
ON CONFLICT (user_id, pattern_type)
|
||
DO UPDATE SET
|
||
pattern_data = EXCLUDED.pattern_data,
|
||
confidence_score = EXCLUDED.confidence_score,
|
||
updated_at = NOW();
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 创建获取用户训练趋势的函数
|
||
CREATE OR REPLACE FUNCTION get_user_training_trends(
|
||
p_user_id uuid,
|
||
p_days INT DEFAULT 30
|
||
)
|
||
RETURNS JSONB AS $$
|
||
DECLARE
|
||
result JSONB := '[]'::JSONB;
|
||
trend_item JSONB;
|
||
BEGIN -- 获取目标完成趋势
|
||
WITH goal_trends AS (
|
||
SELECT
|
||
date_trunc('day', recorded_at) as trend_date,
|
||
COUNT(*) as changes_count,
|
||
AVG(change_amount) as avg_change
|
||
FROM public.ak_goal_progress_history
|
||
WHERE user_id = p_user_id
|
||
AND recorded_at >= NOW() - (p_days * INTERVAL '1 day')
|
||
GROUP BY date_trunc('day', recorded_at)
|
||
ORDER BY trend_date
|
||
)
|
||
SELECT jsonb_agg(
|
||
jsonb_build_object(
|
||
'date', trend_date,
|
||
'type', 'goal_progress',
|
||
'value', avg_change,
|
||
'count', changes_count
|
||
)
|
||
) INTO trend_item
|
||
FROM goal_trends;
|
||
|
||
result = result || COALESCE(trend_item, '[]'::JSONB);
|
||
-- 获取偏好变化趋势
|
||
WITH preference_trends AS (
|
||
SELECT
|
||
date_trunc('day', created_at) as trend_date,
|
||
action_type,
|
||
COUNT(*) as action_count
|
||
FROM public.ak_user_preference_history
|
||
WHERE user_id = p_user_id
|
||
AND created_at >= NOW() - (p_days * INTERVAL '1 day')
|
||
GROUP BY date_trunc('day', created_at), action_type
|
||
ORDER BY trend_date
|
||
)
|
||
SELECT jsonb_agg(
|
||
jsonb_build_object(
|
||
'date', trend_date,
|
||
'type', 'preference_change',
|
||
'action_type', action_type,
|
||
'count', action_count
|
||
)
|
||
) INTO trend_item
|
||
FROM preference_trends;
|
||
|
||
result = result || COALESCE(trend_item, '[]'::JSONB);
|
||
|
||
RETURN result;
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- 创建触发器来自动记录偏好变化
|
||
CREATE OR REPLACE FUNCTION track_preference_changes()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF TG_OP = 'INSERT' THEN
|
||
INSERT INTO public.ak_user_preference_history (
|
||
user_id, sport_type_id, action_type, new_values
|
||
) VALUES (
|
||
NEW.user_id, NEW.sport_type_id, 'created', to_jsonb(NEW)
|
||
);
|
||
RETURN NEW;
|
||
ELSIF TG_OP = 'UPDATE' THEN
|
||
INSERT INTO public.ak_user_preference_history (
|
||
user_id, sport_type_id, action_type, old_values, new_values
|
||
) VALUES (
|
||
NEW.user_id, NEW.sport_type_id, 'updated', to_jsonb(OLD), to_jsonb(NEW)
|
||
);
|
||
RETURN NEW;
|
||
ELSIF TG_OP = 'DELETE' THEN
|
||
INSERT INTO public.ak_user_preference_history (
|
||
user_id, sport_type_id, action_type, old_values
|
||
) VALUES (
|
||
OLD.user_id, OLD.sport_type_id, 'deleted', to_jsonb(OLD)
|
||
);
|
||
RETURN OLD;
|
||
END IF;
|
||
RETURN NULL;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建触发器
|
||
DROP TRIGGER IF EXISTS preference_changes_trigger ON public.ak_user_sport_preferences;
|
||
CREATE TRIGGER preference_changes_trigger
|
||
AFTER INSERT OR UPDATE OR DELETE ON public.ak_user_sport_preferences
|
||
FOR EACH ROW EXECUTE FUNCTION track_preference_changes();
|
||
|
||
-- 创建触发器来自动记录目标进度变化
|
||
CREATE OR REPLACE FUNCTION track_goal_progress_changes()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
IF TG_OP = 'UPDATE' AND OLD.current_value != NEW.current_value THEN
|
||
INSERT INTO public.ak_goal_progress_history (
|
||
goal_id, user_id, old_value, new_value, change_amount, change_type
|
||
) VALUES (
|
||
NEW.id, NEW.user_id, OLD.current_value, NEW.current_value,
|
||
NEW.current_value - OLD.current_value,
|
||
CASE
|
||
WHEN NEW.current_value > OLD.current_value THEN 'increase'
|
||
ELSE 'decrease'
|
||
END
|
||
);
|
||
END IF;
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- 创建目标进度触发器
|
||
DROP TRIGGER IF EXISTS goal_progress_changes_trigger ON public.ak_user_training_goals;
|
||
CREATE TRIGGER goal_progress_changes_trigger
|
||
AFTER UPDATE ON public.ak_user_training_goals
|
||
FOR EACH ROW EXECUTE FUNCTION track_goal_progress_changes();
|
||
|
||
-- 插入一些示例推荐数据
|
||
INSERT INTO public.ak_personalized_recommendations (
|
||
user_id, recommendation_type, title, description, reason, priority
|
||
)
|
||
SELECT
|
||
id as user_id,
|
||
'sport' as recommendation_type,
|
||
'尝试新的运动类型' as title,
|
||
'根据您的偏好，我们推荐您尝试瑜伽或游泳' as description,
|
||
'扩展运动种类有助于全面发展' as reason,
|
||
3 as priority
|
||
FROM public.ak_users
|
||
WHERE role = 'student'
|
||
LIMIT 5;
|