dotnetcore / dotnetcore/FreeSql
internal static ISqlOver<TValue> Over<TValue>(string sqlFunc) 能改为 public吗
- Dominant language
- C#
- Stars
- 4.4k
- Forks
- 910
- PR merge metrics
- No merged PRs in 30d
Description
改为 public 实现如下 语句中的 NTILE 函数
WITH rfm AS (
SELECT
f.member_code,
v.member_level_name,
-- 计算R(最近消费距离今天的天数)
EXTRACT(DAY FROM CURRENT_DATE - MAX(f.sale_time)) AS "r_value",
-- 计算F(消费频次)
COUNT(DISTINCT f.sale_order_code) AS "f_value",
-- 计算M(消费金额)
SUM(f.sale_amt) AS "m_value"
FROM erp_flow f
LEFT JOIN erp_vip v ON f.member_code = v.member_card_no
WHERE f.member_code IS NOT NULL
AND f.sale_type = 1
GROUP BY f.member_code, v.member_level_name
),
rfm_score AS (
SELECT
*,
-- R值越低越好,得分反向排序(1-5分)
NTILE(5) OVER (ORDER BY "r_value" DESC) AS "r_score",
-- F和M值越高越好,正向排序(1-5分)
NTILE(5) OVER (ORDER BY "f_value") AS "f_score",
NTILE(5) OVER (ORDER BY "m_value") AS "m_score"
FROM rfm
)
-- 定义会员分层(基于RFM得分组合)
SELECT
member_level_name AS "会员等级",
CASE
WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN '高价值会员'
WHEN r_score >= 3 AND (f_score >= 3 OR m_score >= 3) THEN '潜力会员'
WHEN f_score >= 3 AND m_score >= 3 AND r_score < 3 THEN '召回会员'
ELSE '低价值会员'
END AS "会员分层",
COUNT(DISTINCT member_code) AS "会员数量",
ROUND(COUNT(DISTINCT member_code) * 100.0 / SUM(COUNT(DISTINCT member_code)) OVER (PARTITION BY member_level_name), 2) AS "分层占比(%)"
FROM rfm_score
GROUP BY member_level_name, "会员分层"
ORDER BY member_level_name, "会员数量" DESC;
Contributor guide
No contributing guide indexed for this repository
Research direction
Search the repository for the declaration of internal static ISqlOver Over(string sqlFunc). Read its surrounding SQL-function API and check how the reported NTILE query is expected to call it. Done means the method is publicly accessible and the NTILE-based SQL can be represented without access errors.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- backend, databases
- Issue type
- Feature
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100