dotnetcore / dotnetcore/FreeSql

internal static ISqlOver<TValue> Over<TValue>(string sqlFunc) 能改为 public吗

Open
#2,071 1 comment 0 reactions 0 assignees View on GitHub
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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.