### Install Project Dependencies (Bash)

Source: https://github.com/suwei8/lotto_ai4/blob/master/README.md

Installs the necessary Python packages for the Lotto AI 4 project using pip and a requirements file.

```bash
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

--------------------------------

### Run Streamlit Application (Bash)

Source: https://github.com/suwei8/lotto_ai4/blob/master/README.md

Starts the Lotto AI 4 Streamlit application. The application performs a database connectivity check on startup.

```bash
streamlit run app.py
```

--------------------------------

### Get Lottery Draw Information (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberAnalysis_requirement.md

Fetches the opening code and opening time for a given lottery issue. This information is displayed alongside the analysis results for easy comparison.

```sql
SELECT issue_name, open_code, open_time FROM lotto_3d.lottery_results WHERE issue_name = :issue_name;
```

--------------------------------

### Get Playtype Names by IDs (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberAnalysis_requirement.md

Maps playtype IDs to their corresponding names. This is useful for displaying human-readable playtype information to the user.

```sql
SELECT playtype_id, playtype_name FROM lotto_3d.playtype_dict WHERE playtype_id IN (:playtype_ids) ORDER BY playtype_id;
```

--------------------------------

### Get Available Playtypes for an Issue (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberAnalysis_requirement.md

Fetches the distinct playtype IDs available for a specific lottery issue. This is used to determine which game types can be analyzed for a given period.

```sql
SELECT DISTINCT playtype_id FROM lotto_3d.expert_predictions WHERE issue_name = :issue_name ORDER BY playtype_id;
```

--------------------------------

### Get Latest Lottery Issue Name (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberAnalysis_requirement.md

Retrieves the most recent lottery issue name from the database. This query is essential for fetching the latest data for analysis.

```sql
SELECT issue_name FROM lotto_3d.lottery_results ORDER BY open_time DESC, issue_name DESC LIMIT 1;
```

--------------------------------

### SQL: Get Distinct Issue Names

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_requirement.md

Retrieves a distinct list of issue names from the expert_predictions table, ordered in descending order. This list is used to populate the issue selection dropdown.

```sql
SELECT DISTINCT issue_name FROM expert_predictions ORDER BY issue_name DESC
```

--------------------------------

### SQL: Get Playtype List and Names

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_requirement.md

Retrieves a distinct list of playtype IDs and their corresponding names for a given issue name from the expert_predictions and playtype_dict tables. This is used to populate the playtype selection dropdown.

```sql
SELECT DISTINCT ep.playtype_id, pd.playtype_name
FROM expert_predictions ep
JOIN playtype_dict pd ON pd.playtype_id = ep.playtype_id
WHERE ep.issue_name = ?
ORDER BY ep.playtype_id;
```

--------------------------------

### SQL: Get Multi-Playtype Prediction Details

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/Playtype_CombinationView_requirement.md

Fetches detailed prediction data (user ID, playtype ID, and numbers) for a specific issue name and a list of playtype IDs from the expert predictions table. This is a core query for detailed analysis.

```sql
SELECT user_id, playtype_id, numbers FROM lotto_3d.expert_predictions WHERE issue_name = :issue_name AND playtype_id IN (:playtype_ids);
```

--------------------------------

### SQL: Get Lottery Results

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_requirement.md

Retrieves the lottery results for a given issue name, including the winning code, sum, span, odd-even ratio, big-small ratio, and open time. This is used to display the official results for comparison.

```sql
SELECT open_code, sum, span, odd_even_ratio, big_small_ratio, open_time
FROM lottery_results
WHERE issue_name = ?
LIMIT 1;
```

--------------------------------

### Get Expert Predictions for an Issue and Playtype (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberAnalysis_requirement.md

Retrieves detailed expert predictions (user ID and numbers) for a specific lottery issue and playtype. Duplicate number combinations are handled at the application layer.

```sql
SELECT user_id, numbers FROM lotto_3d.expert_predictions WHERE issue_name = :issue_name AND playtype_id = :playtype_id;
```

--------------------------------

### SQL Query for Expert Hit Rankings

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/ExpertHitTop_requirement.md

Retrieves expert hit statistics, including nickname, playtype, total counts, hit counts, and hit rate. It allows filtering by issue name, playtype, and supports pagination. The query joins expert_hit_stat with expert_info to get expert nicknames.

```sql
SELECT s.user_id,
       i.nick_name,
       s.playtype_name,                  -- 若演进为 playtype_id，则关联字典取名
       s.total_count,
       s.hit_count,
       s.hit_number_count,
       s.avg_hit_gap,
       ROUND(s.hit_count / NULLIF(s.total_count,0), 4) AS hit_rate
FROM lotto_3d.expert_hit_stat s
LEFT JOIN lotto_3d.expert_info i ON i.user_id = s.user_id
WHERE s.issue_name BETWEEN :start_issue AND :end_issue
  /* AND (:playtype_name IS NULL OR s.playtype_name = :playtype_name) */
ORDER BY hit_rate DESC, s.hit_count DESC, s.avg_hit_gap ASC
LIMIT :limit OFFSET :offset;
```

--------------------------------

### SQL: Get Number Distribution Data

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_requirement.md

Fetches the 'number set' (num) and its value (val) from the red_val_list table for a specific issue name and a list of selected playtype IDs. The results are ordered by 'id' in descending order for stable display.

```sql
SELECT playtype_id, issue_name, num, val, user_id, id
FROM red_val_list
WHERE issue_name = ?
  AND playtype_id IN (?, ?, ...)
ORDER BY id DESC;
```

--------------------------------

### SQL: Query Expert Recommendations with Playtype Names

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/Userid_Query_requirement.md

Fetches distinct playtype names and corresponding prediction numbers for a given issue name and user ID. It joins the expert_predictions table with playtype_dict to get the playtype names and orders the results by playtype ID.

```SQL
SELECT DISTINCT pd.playtype_name, ep.numbers
FROM expert_predictions ep
JOIN playtype_dict pd ON pd.playtype_id = ep.playtype_id
WHERE ep.issue_name = ? AND ep.user_id = ?
ORDER BY pd.playtype_id;
```

--------------------------------

### Initialize Project Structure and Dependencies (Python)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/development_plan.md

Sets up the project directory structure and specifies essential Python dependencies for data analysis and web application development using Streamlit.

```Python
import streamlit as st
import sqlalchemy
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import mysqlclient # or pymysql
```

--------------------------------

### Python: 命中判断方法示例

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertFilterPlus_requirement.md

此处为概念性描述，实际实现需要根据彩种和玩法规则来判定。该函数或逻辑会接收专家推荐的数字和当期开奖号码，并返回是否命中。

```python
# 示例：模拟命中判断逻辑
# def check_hit(playtype_id, recommended_numbers, open_code):
#     # 根据 playtype_id 调用不同的命中规则
#     if playtype_id == '3D_direct':
#         return recommended_numbers == open_code
#     # ... 其他玩法
#     return False
```

--------------------------------

### Dockerfile for Streamlit Application

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/development_plan.md

Provides a Dockerfile to containerize the Streamlit application, specifying the base image, dependencies, and the command to run the application.

```Dockerfile
FROM python:3.12

WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY . .

EXPOSE 8501

CMD ["streamlit", "run", "your_app_file.py"]
```

--------------------------------

### Unit Testing with Python

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/development_plan.md

Outlines the approach for writing unit tests for the utility and database layers of the project to ensure code correctness and reliability.

```Python
import unittest
from utils import your_utility_module
from db import connection

class TestUtils(unittest.TestCase):
    def test_pagination(self):
        # Test pagination logic
        pass

class TestDatabase(unittest.TestCase):
    def test_db_connection(self):
        # Test database connection
        pass

if __name__ == '__main__':
    unittest.main()
```

--------------------------------

### SQL: 查询本期推荐记录

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertFilterPlus_requirement.md

根据期号、玩法和一组用户 ID，查询本期的专家推荐记录，包括用户 ID 和推荐的数字。

```sql
-- placeholder 由 user_ids 大小动态生成
SELECT user_id, numbers
FROM expert_predictions
WHERE issue_name = ?
  AND playtype_id = ?
  AND user_id IN (?, ?, ...);
```

--------------------------------

### SQL: 查询专家昵称映射

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertFilterPlus_requirement.md

查询所有专家的用户 ID 和昵称，用于将用户 ID 映射到具体的专家名称。

```sql
SELECT user_id, nick_name FROM expert_info;
```

--------------------------------

### SQL Query Best Practice (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/README.md

Ensures all SQL queries are executed via the `query_db` function using parameterized syntax (`:param`) for security, auditability, and performance.

```sql
All SQL must be called via query_db, ensuring the use of parameterized (':param') syntax for read-only, auditable, and performance requirements.
```

--------------------------------

### Run Pytest Unit Tests (Bash)

Source: https://github.com/suwei8/lotto_ai4/blob/master/README.md

Executes the project's unit tests using pytest. By default, tests skip real database interactions.

```bash
python -m pytest
```

--------------------------------

### Database Connection and Query Utility (Python)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/development_plan.md

Provides a Python function to establish a connection to the 'lotto_3d' database and a generic query function for executing SQL queries with parameterized inputs, ensuring security against SQL injection.

```Python
from sqlalchemy import create_engine

DB_CONNECTION_STRING = "mysql+mysqlclient://user:password@host/lotto_3d"

def get_db_connection():
    return create_engine(DB_CONNECTION_STRING)

def query_db(sql: str, params: dict) -> pd.DataFrame:
    engine = get_db_connection()
    with engine.connect() as connection:
        result = connection.execute(sql, params)
        return pd.DataFrame(result.fetchall(), columns=result.keys())
```

--------------------------------

### SQL: 筛选专家 - 不包含任何数字

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertFilterPlus_requirement.md

根据指定的期号和玩法，筛选出推荐数字不包含任何给定数字的专家 ID。

```sql
SELECT DISTINCT user_id
FROM expert_predictions
WHERE issue_name = ?
  AND playtype_id = ?
  AND (numbers NOT LIKE ? AND numbers NOT LIKE ? ...);
```

--------------------------------

### Run Pytest with Database Tests (Bash)

Source: https://github.com/suwei8/lotto_ai4/blob/master/README.md

Executes unit tests that require a connection to the actual MySQL database by setting the RUN_DB_TESTS environment variable.

```bash
RUN_DB_TESTS=1 python -m pytest
```

--------------------------------

### 获取期号列表

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_v2_requirement.md

从red_val_list_v2表中按期号倒序查询所有不重复的期号。此查询用于提供用户选择期号的下拉列表。

```sql
SELECT DISTINCT issue_name
FROM red_val_list_v2
ORDER BY issue_name DESC;
```

--------------------------------

### SQL: 筛选专家 - 包含所有数字

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertFilterPlus_requirement.md

根据指定的期号和玩法，筛选出推荐数字包含所有给定数字的专家 ID。

```sql
SELECT DISTINCT user_id
FROM expert_predictions
WHERE issue_name = ?
  AND playtype_id = ?
  AND (numbers LIKE ? AND numbers LIKE ? ...);
```

--------------------------------

### Streamlit App Structure (Python)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserHitAnalysis_requirement.md

Basic structure for a Streamlit application, demonstrating the use of session state, caching, and potentially component extensions for enhanced interactivity and performance.

```python
import streamlit as st

# Initialize session state if not already done
if 'initialized' not in st.session_state:
    st.session_state.initialized = True
    # Initialize other session state variables as needed

@st.cache_data
def load_data():
    # Placeholder for data loading and processing
    pass

def main():
    st.title("Lotto AI User Hit Analysis")
    
    # UI elements for user input (e.g., issue selection, playtype, user_id)
    
    # Call data loading and analysis functions
    # Display results using Streamlit components (tables, charts)
    pass

if __name__ == "__main__":
    main()

```

--------------------------------

### SQL: 筛选专家 - 包含任意数字

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertFilterPlus_requirement.md

根据指定的期号和玩法，筛选出推荐数字包含任意给定数字的专家 ID。

```sql
SELECT DISTINCT user_id
FROM expert_predictions
WHERE issue_name = ?
  AND playtype_id = ?
  AND (numbers LIKE ? OR numbers LIKE ? ...);
```

--------------------------------

### Python: Streamlit 页面结构与状态管理

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

展示了使用Streamlit构建用户界面的基本结构，包括页面标题、模块划分和session_state的使用。session_state允许在用户会话期间保持状态，便于管理用户选择和数据。

```python
import streamlit as st

# 设置页面配置
st.set_page_config(layout="wide")

# 初始化 session_state
if 'selected_issues' not in st.session_state:
    st.session_state.selected_issues = []
if 'selected_playtype' not in st.session_state:
    st.session_state.selected_playtype = None

# 页面标题
st.title("【AI命中统计分析】UserExpertHitStat")

# 模块A：基础选择
st.header("模块A：基础选择")
# ... (此处应包含期号和玩法的选择组件)

# 模块B：命中统计聚合
st.header("模块B：命中统计聚合")
# ... (此处应展示聚合后的统计数据)

# 模块C：命中分布可视化
st.header("模块C：命中分布可视化")
# ... (此处应展示命中分布图)

# 模块D：筛选并反查推荐记录
st.header("模块D：筛选并反查推荐记录")
# ... (此处应包含筛选条件和反查组件)

# 模块E：可视化与详情
st.header("模块E：可视化与详情")
# ... (此处应展示热力图和推荐详情表)

```

--------------------------------

### Streamlit 缓存数据装饰器

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_v2_requirement.md

使用Streamlit的`@st.cache_data`装饰器来缓存数据查询结果，以提高性能和用户体验。这对于频繁访问相同数据的函数尤其有用。

```python
import streamlit as st

@st.cache_data
def get_data(query):
    # 执行数据库查询并返回结果
    pass
```

--------------------------------

### Configure Database Connection (Python)

Source: https://github.com/suwei8/lotto_ai4/blob/master/README.md

Specifies the database connection string using SQLAlchemy and PyMySQL for a read-only MySQL 8.0 instance within a Docker container.

```python
mysql+pymysql://root:sw63828@mysql:3306/lotto_3d?charset=utf8mb4
```

--------------------------------

### Python: 使用 st.cache_data 优化数据加载

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

演示了如何使用Streamlit的`st.cache_data`装饰器来缓存数据查询结果。这对于避免重复执行数据库查询，提高应用性能至关重要，尤其是在数据量大或查询复杂时。

```python
import streamlit as st
import pandas as pd
# 假设有一个函数用于从数据库获取数据
def fetch_data_from_db(query):
    # 模拟数据库查询
    # Replace with actual database query logic
    print(f"Executing query: {query}")
    # 示例返回一个DataFrame
    return pd.DataFrame({
        'col1': [1, 2], 
        'col2': [3, 4]
    })

@st.cache_data
def get_expert_hit_stats(issues, playtype):
    """获取专家命中统计数据，并缓存结果."""
    query = f"SELECT user_id, SUM(total_count) AS total_count, SUM(hit_count) AS hit_count, SUM(hit_number_count) AS hit_number_count FROM expert_hit_stat WHERE issue_name IN ({','.join(['?']*len(issues))}) AND playtype_name = ? GROUP BY user_id;"
    # 实际应用中需要替换 ? 为参数
    return fetch_data_from_db(query) # 替换为实际的SQL查询

# 在Streamlit应用中使用
selected_issues = ['20230101', '20230102']
selected_playtype = 'playtype_A'

stats_df = get_expert_hit_stats(selected_issues, selected_playtype)
st.dataframe(stats_df)

```

--------------------------------

### Python: Streamlit App Structure

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/Playtype_CombinationView_requirement.md

Basic structure of a Streamlit application, demonstrating the use of session state and caching for interactive data display and analysis. This serves as the foundation for the Playtype Combination View.

```python
import streamlit as st

# Initialize session state if it doesn't exist
if 'selected_issue' not in st.session_state:
    st.session_state.selected_issue = None
if 'selected_playtypes' not in st.session_state:
    st.session_state.selected_playtypes = []

@st.cache_data
def get_latest_issue():
    # Placeholder for SQL query to get latest issue
    return "2025249"

def main():
    st.title("Playtype Combination View")

    # Example of using session state and caching
    latest_issue = get_latest_issue()
    st.write(f"Latest Issue: {latest_issue}")

    # More Streamlit components for selection and display would go here

if __name__ == "__main__":
    main()

```

--------------------------------

### Fetch Expert Recommendations with Playtype Name (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserHitAnalysis_requirement.md

Retrieves all recommendations for a given expert within a specified set of issues, joining with playtype_dict to include playtype names. Used for expert profile aggregation.

```sql
SELECT ep.issue_name, ep.playtype_id, pd.playtype_name, ep.numbers
FROM expert_predictions ep
JOIN playtype_dict pd ON pd.playtype_id = ep.playtype_id
WHERE ep.user_id = ? AND ep.issue_name IN (?,?,...);
```

--------------------------------

### Python/Streamlit: Data Fetching and Display

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/Userid_Query_requirement.md

Illustrates the use of Streamlit components and caching for efficient data retrieval and display. It involves querying predictions and lottery results, and potentially handling data standardization. Dependencies include Streamlit and database connectors.

```Python
# Example placeholder for Streamlit implementation
# import streamlit as st
# from database import get_predictions, get_lottery_results

# @st.cache_data
def load_data(issue_name, user_id):
    predictions = get_predictions(issue_name, user_id)
    results = get_lottery_results(issue_name)
    # Process and standardize 'numbers' if needed
    return predictions, results

# issue_name = st.text_input("Enter Issue Name")
# user_id = st.text_input("Enter User ID")

# if issue_name and user_id:
#     predictions, results = load_data(issue_name, user_id)
#     st.dataframe(predictions)
#     st.json(results)

```

--------------------------------

### SQL: 获取最新期号

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/HotCold_requirement.md

查询lotto_3d.lottery_results表中最新的期号。通过open_time和issue_name降序排列并取第一条记录来实现。

```sql
SELECT issue_name
FROM lotto_3d.lottery_results
ORDER BY open_time DESC, issue_name DESC
LIMIT 1;
```

--------------------------------

### SQL: 查询期号候选

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

合并expert_hit_stat和expert_predictions表中的期号，生成一个包含所有相关期号的列表，用于下拉选择。结果按期号降序排列。

```sql
SELECT DISTINCT issue_name FROM expert_hit_stat
UNION
SELECT DISTINCT issue_name FROM expert_predictions
ORDER BY issue_name DESC;
```

--------------------------------

### Fetch Playtype List (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/System_Requirement_Specification.md

Retrieves a list of all available playtypes, including their IDs and names, from the playtype dictionary. The results are ordered by playtype ID, ensuring a consistent and predictable list for use in the application. This is crucial for filtering and categorization.

```sql
SELECT playtype_id, playtype_name
FROM lotto_3d.playtype_dict
ORDER BY playtype_id;
```

--------------------------------

### Expert Hit Ranking (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/System_Requirement_Specification.md

Generates a ranking of experts based on their hit rate for a specified range of issues and an optional playtype. It joins expert hit statistics with expert information to display user IDs, nicknames, hit rates, and other performance metrics. Optimized with indexing and aggregation for fast retrieval.

```sql
SELECT s.user_id,
       i.nick_name,
       s.playtype_name,                         -- 演进后可改为 playtype_id 再关联取名
       s.total_count, s.hit_count,
       s.hit_number_count, s.avg_hit_gap,
       ROUND(s.hit_count / NULLIF(s.total_count,0), 4) AS hit_rate
FROM lotto_3d.expert_hit_stat s
LEFT JOIN lotto_3d.expert_info i ON i.user_id = s.user_id
WHERE s.issue_name BETWEEN :start_issue AND :end_issue
  /* AND (:playtype_name IS NULL OR s.playtype_name = :playtype_name) */
ORDER BY hit_rate DESC, s.hit_count DESC, s.avg_hit_gap ASC
LIMIT :limit OFFSET :offset;
```

--------------------------------

### Fetch Expert Recommendations for a Single Period (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserHitAnalysis_requirement.md

Retrieves the recommended numbers for a specific expert, issue, and playtype from the expert_predictions table. Used in per-issue hit analysis.

```sql
SELECT numbers
FROM expert_predictions
WHERE issue_name = ? AND user_id = ? AND playtype_id = ?;
```

--------------------------------

### Caching Decorator with Streamlit (Python)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/development_plan.md

Implements caching for utility functions using Streamlit's data caching decorator to improve performance by storing and reusing the results of expensive function calls.

```Python
import streamlit as st

@st.cache_data
def get_data(query):
    # Simulate a data fetching operation
    return pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]})

# Usage:
data = get_data("SELECT * FROM table")
```

--------------------------------

### 拉取选号分布数据

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_v2_requirement.md

根据指定的期号和玩法ID列表，从red_val_list_v2表中批量拉取选号分布的详细数据，包括号码集合、命中统计等。此查询是展示主体数据的基础。

```sql
SELECT id, user_id, playtype_id, issue_name,
       num, val, type,
       rank_count,
       hit_count_map,
       serial_hit_count_map,
       series_not_hit_count_map,
       max_serial_hit_count_map,
       max_series_not_hit_count_map,
       his_max_serial_hit_count_map,
       his_max_series_not_hit_count_map
FROM red_val_list_v2
WHERE issue_name = ?
  AND playtype_id IN (?, ?, ...)
ORDER BY id DESC;
```

--------------------------------

### Fetch Playtype List (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserHitAnalysis_requirement.md

Retrieves a list of playtype IDs and their corresponding names from the playtype_dict table, ordered by playtype ID. Used for selecting game types.

```sql
SELECT playtype_id, playtype_name FROM playtype_dict ORDER BY playtype_id;
```

--------------------------------

### SQL: 查询期号列表

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

从expert_hit_stat表中查询所有不重复的期号名称，并按降序排列。这是用于统计分析的期号列表的来源。

```sql
SELECT DISTINCT issue_name FROM expert_hit_stat ORDER BY issue_name DESC;
```

--------------------------------

### SQL: 查询上期命中状态

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

查询指定期号和玩法的专家命中情况，以判断用户的“上期命中状态”。根据hit_count的值来确定是否命中。

```sql
SELECT user_id, hit_count
FROM expert_hit_stat
WHERE issue_name = ? AND playtype_name = ?;
-- 命中：hit_count > 0；未命中：hit_count = 0
```

--------------------------------

### SQL: 命中统计聚合

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

按用户ID聚合expert_hit_stat表中的数据，计算总预测期数、命中期数和命中数字数量。该查询用于统计模块B，并根据指定的期号集合和玩法进行筛选。

```sql
SELECT user_id,
       SUM(total_count) AS total_count,
       SUM(hit_count) AS hit_count,
       SUM(hit_number_count) AS hit_number_count
FROM expert_hit_stat
WHERE issue_name IN (?, ?, ...)
  AND playtype_name = ?
GROUP BY user_id;
```

--------------------------------

### SQL: 查询本期推荐记录

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

根据指定的期号、玩法ID和用户ID列表，从expert_predictions表中查询专家的推荐号码。这用于反查推荐详情。

```sql
SELECT user_id, numbers
FROM expert_predictions
WHERE issue_name = ?
  AND playtype_id = ? -- 或以名称等价过滤
  AND user_id IN (?, ?, ...);
```

--------------------------------

### SQL: 查询玩法列表

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserExpertHitStat_requirement.md

根据指定的期号，从expert_hit_stat表中查询所有不重复的玩法名称。这用于构建前端的玩法选择列表。

```sql
SELECT DISTINCT playtype_name FROM expert_hit_stat WHERE issue_name = ?;
```

--------------------------------

### SQL: 获取指定期段数据

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/HotCold_requirement.md

查询lotto_3d.lottery_results表中指定期号范围（issue_name BETWEEN :issue_start AND :issue_end）的数据，包含issue_name, open_code, open_time。

```sql
SELECT issue_name, open_code, open_time
FROM lotto_3d.lottery_results
WHERE issue_name BETWEEN :issue_start AND :issue_end
ORDER BY issue_name DESC;
```

--------------------------------

### 获取玩法及其名称

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/RedValList_v2_requirement.md

根据指定的期号，从red_val_list_v2和playtype_dict表中联查，获取该期号下存在数据的玩法ID和玩法名称。此查询用于填充玩法选择器。

```sql
SELECT DISTINCT v2.playtype_id, pd.playtype_name
FROM red_val_list_v2 v2
JOIN playtype_dict pd ON pd.playtype_id = v2.playtype_id
WHERE v2.issue_name = ?
ORDER BY v2.playtype_id;
```

--------------------------------

### Number Distribution Analysis (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/System_Requirement_Specification.md

Retrieves data for number distribution analysis (v2), focusing on a specific issue name and playtype. It selects records from the red_val_list_v2 table, ordered by ID, playtype ID, and issue name, with pagination. This helps in understanding the frequency and distribution of numbers.

```sql
SELECT id, user_id, playtype_id, issue_name,
       num, val, type, rank_count
FROM lotto_3d.red_val_list_v2
WHERE issue_name = :issue_name
  AND playtype_id = :playtype_id
ORDER BY id DESC, playtype_id ASC, issue_name DESC
LIMIT :limit OFFSET :offset;
```

--------------------------------

### SQL: 获取指定日期范围数据

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/HotCold_requirement.md

查询lotto_3d.lottery_results表中指定日期范围（open_time BETWEEN :start_dt AND :end_dt）的数据，包含issue_name, open_code, open_time。

```sql
SELECT issue_name, open_code, open_time
FROM lotto_3d.lottery_results
WHERE open_time BETWEEN :start_dt AND :end_dt
ORDER BY open_time DESC, issue_name DESC;
```

--------------------------------

### Fetch Playtype Names (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberHeatmap_Simplified_v2_all_requirement.md

Retrieves playtype IDs and their corresponding names from the lotto_3d.playtype_dict table. It filters by a list of playtype IDs and orders the results by playtype ID.

```SQL
SELECT playtype_id, playtype_name
FROM lotto_3d.playtype_dict
WHERE playtype_id IN (:playtype_ids)
ORDER BY playtype_id;
```

--------------------------------

### Fetch Multi-Issue Predictions (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberHeatmap_Simplified_v2_all_requirement.md

Fetches prediction data (issue name, user ID, numbers) for specified issues and a particular playtype from the lotto_3d.expert_predictions table. This query is essential for analyzing recommendation patterns.

```SQL
SELECT issue_name, user_id, numbers
FROM lotto_3d.expert_predictions
WHERE issue_name IN (:issues) AND playtype_id = :playtype_id;
```

--------------------------------

### SQL Query for Playtype Dropdown List

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/ExpertHitTop_requirement.md

Retrieves playtype IDs and names from the playtype_dict table. This query is used to populate dropdown menus for selecting different playtypes, ordered by playtype ID.

```sql
SELECT playtype_id, playtype_name
FROM lotto_3d.playtype_dict

ORDER BY playtype_id;
```

--------------------------------

### Expert Prediction vs. Lottery Result Detail (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/System_Requirement_Specification.md

Provides a detailed comparison between expert predictions and actual lottery results for a specific user and playtype within a given issue range. It joins expert predictions with lottery results tables on issue name to show predicted numbers against the open code. Essential for analyzing expert performance on a per-issue basis.

```sql
SELECT p.issue_name, p.playtype_id, p.numbers,
       r.open_code, r.open_time
FROM lotto_3d.expert_predictions p
JOIN lotto_3d.lottery_results r
  ON r.issue_name = p.issue_name
WHERE p.user_id = :user_id
  AND p.playtype_id = :playtype_id
  AND p.issue_name BETWEEN :start_issue AND :end_issue
ORDER BY p.issue_name DESC;
```

--------------------------------

### MySQL Query Workaround for LIMIT and IN Subquery Incompatibility

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/FilterTool_MissV2_requirement.md

Demonstrates alternative SQL query patterns to overcome MySQL version incompatibility issues with LIMIT clauses in subqueries used with IN statements. These methods use temporary tables or derived tables with JOINs to achieve the desired result.

```sql
-- Step1: First retrieve the user_id list (limit the quantity)
SELECT DISTINCT user_id
INTO TEMPORARY TABLE tmp_users
FROM lotto_3d.expert_predictions
WHERE issue_name = :issue_name AND playtype_id IN (:playtype_ids)
LIMIT :M;

-- Step2: Then use JOIN to get nicknames (avoid LIMIT in IN)
SELECT i.user_id, i.nick_name
FROM lotto_3d.expert_info i
JOIN tmp_users u ON u.user_id = i.user_id
LIMIT :M;

-- Or a derived table JOIN without temporary tables
SELECT i.user_id, i.nick_name
FROM lotto_3d.expert_info i
JOIN (
  SELECT DISTINCT user_id
  FROM lotto_3d.expert_predictions
  WHERE issue_name = :issue_name AND playtype_id IN (:playtype_ids)
  LIMIT :M
) u ON u.user_id = i.user_id
LIMIT :M;
```

--------------------------------

### Fetch Lottery Open Code (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserHitAnalysis_requirement.md

Retrieves the open code (winning numbers) for a specific issue from the lottery_results table. Used to determine hits.

```sql
SELECT open_code FROM lottery_results WHERE issue_name = ?;
```

--------------------------------

### SQL Query for Expert Prediction Drill-down

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/ExpertHitTop_requirement.md

Fetches detailed expert predictions for specific users and playtypes, comparing them against lottery results on a per-issue basis. It joins expert_predictions with lottery_results to display prediction numbers, open codes, and open times, ordered by issue name.

```sql
SELECT p.issue_name, p.numbers, r.open_code, r.open_time
FROM lotto_3d.expert_predictions p
JOIN lotto_3d.lottery_results r ON r.issue_name = p.issue_name
WHERE p.user_id = :user_id
  AND p.playtype_id = :playtype_id
  AND p.issue_name BETWEEN :start_issue AND :end_issue
ORDER BY p.issue_name DESC;
```

--------------------------------

### Fetch Latest Lottery Results (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/NumberHeatmap_Simplified_v2_all_requirement.md

Retrieves the issue name, open code, and open time for the N most recent lottery results from the lotto_3d.lottery_results table. It orders the results by open time in descending order, then by issue name.

```SQL
SELECT issue_name, open_code, open_time
FROM lotto_3d.lottery_results
ORDER BY open_time DESC, issue_name DESC
LIMIT :N;
```

--------------------------------

### Fetch Distinct Issue Names (SQL)

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/UserHitAnalysis_requirement.md

Retrieves a distinct list of issue names from the expert_predictions table, ordered in descending order. Used for selecting issue sets.

```sql
SELECT DISTINCT issue_name FROM expert_predictions ORDER BY issue_name DESC;
```

--------------------------------

### SQL: Fetch Expert Nickname

Source: https://github.com/suwei8/lotto_ai4/blob/master/docs/requirements/Userid_Query_requirement.md

Retrieves the nickname of an expert based on their user ID from the expert_info table. This is an optional field used for displaying expert details.

```SQL
SELECT nick_name FROM expert_info WHERE user_id = ? LIMIT 1;
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.