在phpMyAdmin中编写SQL计算实验数据多参数的技术求助

Got it, let's work through this to build your complete SQL query. Since several of your calculations rely on comparing the current row to the previous one, we'll use MySQL's LAG() window function to fetch prior-row values—this is key for replicating those Excel-style row-to-row calculations efficiently.

Here's the full query that covers all 8 parameters, extending your initial statement:

WITH row_context AS (
    SELECT
        id,
        type,
        charSq,
        xCoordinate,
        yCoordinate,
        distance,
        speed,
        timestamp,
        -- Fetch previous row values for cross-row calculations
        LAG(xCoordinate) OVER (ORDER BY id) AS prev_x,
        LAG(yCoordinate) OVER (ORDER BY id) AS prev_y,
        LAG(substr(charSq, -1, 1)) OVER (ORDER BY id) AS prev_typed_char
    FROM table1
)
SELECT
    id,
    type,
    charSq,
    -- 1. Last input character (your original implementation)
    substr(charSq, -1, 1) AS TypedChar,
    xCoordinate,
    yCoordinate,
    angle,
    distance,
    timestamp,
    speed,
    -- 2. Slope: (B3-B2)/(A3-A2)
    (yCoordinate - prev_y)/NULLIF(xCoordinate - prev_x, 0) AS Slope,
    -- 3. Intercept: B2 - (A2 * Slope)
    prev_y - (prev_x * (yCoordinate - prev_y)/NULLIF(xCoordinate - prev_x, 0)) AS Intercept,
    -- 4. Angle (degrees): MOD(DEGREES(ATAN2((A3-A2),(B3-B2))), 360)
    MOD(DEGREES(ATAN2(xCoordinate - prev_x, yCoordinate - prev_y)), 360) AS AngleDegrees,
    -- 5. Difficulty Index: LOG(((E1/7.1)+1),2) (assuming E1 = distance column)
    LOG(2, (distance/7.1) + 1) AS DifficultyIndex,
    -- 6. Speed value length flag: IF(LEN(D3) >=3, "1","0") (assuming D3 = speed column)
    IF(CHAR_LENGTH(CAST(speed AS CHAR)) >= 3, '1', '0') AS SpeedLengthFlag,
    -- 7. Error sequence flag: IF(I3=I2,"Search Time","Actual Time") (assuming I = TypedChar)
    IF(substr(charSq, -1, 1) = prev_typed_char, 'Search Time', 'Actual Time') AS ErrorSequenceFlag,
    -- 8. Character marker: IF(I2="A",1, IF(I2="B",2, IF(I2="C",3, 0))) (assuming I = TypedChar)
    CASE
        WHEN substr(charSq, -1, 1) = 'A' THEN 1
        WHEN substr(charSq, -1, 1) = 'B' THEN 2
        WHEN substr(charSq, -1, 1) = 'C' THEN 3
        ELSE 0
    END AS CharFlag
FROM row_context
ORDER BY id;

Key Details & Explanations: #

• Window Functions: The LAG() function pulls values from the immediately preceding row, ordered by id. If your data's row order should be based on timestamp instead, swap ORDER BY id with ORDER BY timestamp.
• Division Safety: NULLIF(xCoordinate - prev_x, 0) prevents division-by-zero errors when the x-coordinate doesn't change between rows (returns NULL for Slope/Intercept in these cases instead of crashing the query).
• Angle Calculation: MySQL's ATAN2(x, y) matches Excel's ATAN2(x_num, y_num) parameter order, so we pass the x-difference first, then y-difference. MOD() ensures the angle stays within the 0-360 degree range.
• Base-2 Log: MySQL 8.0+ supports LOG(base, number) directly, so we use LOG(2, ...) to replicate Excel's base-2 logarithm.
• Speed Length Check: We cast speed to a string first (since it's likely numeric) and use CHAR_LENGTH to count characters (avoids confusion between byte-length and character-length for numeric values).
• Character Marker: A CASE statement replaces nested IFs for better readability and easier future adjustments.

Quick Fix for Older MySQL Versions: #

If your MySQL version is pre-8.0 (no CTE support with WITH), you can rewrite the query using correlated subqueries to fetch LAG() values. Just let me know if you need that adjusted!

内容的提问来源于stack exchange，提问作者Rupesh Nath