Startrekzky opened a new issue, #7306: URL: https://github.com/apache/incubator-devlake/issues/7306
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-devlake/issues?q=is%3Aissue) and found no similar feature requirement. ### Use case The existing approach to generate this table is the active SQL. If I want to add more week's data, I'll have to copy tons of code.  However, I can't directly use the dynamic SQL in Grafana as it only has the read-only permission. Therefore, I'm thinking if we can provide an embedded procedure to allow end users to call this procedure. ### Description ## Prerequisite: Create a mock dataset for the dynamic SQL to run on The SQL is ``` DROP TABLE IF EXISTS _worklogs; CREATE TABLE _worklogs AS with _accounts as ( select ua.account_id, ua.user_id, u.name from accounts a join user_accounts ua on a.id = ua.account_id join users u on ua.user_id = u.id where ua.user_id in (1,2) ), _activities as ( SELECT *, ROW_NUMBER() OVER (PARTITION BY `Date` ORDER BY `Time` desc) AS _row_number FROM ( SELECT DATE(created_date) as Date, created_date as Time, 'Create an issue' as Activity, concat('#', issue_key, ' ', title) as Details, a.name as Name FROM issues i join _accounts a on i.creator_id = a.account_id where created_date > '2023-10-09 19:15:28' union SELECT DATE(resolution_date) as Date, resolution_date as Time, 'Issue resolved' as Activity, concat('#', issue_key, ' ', title) as Details, a.name as Name FROM issues i join _accounts a on i.assignee_id = a.account_id where resolution_date > '2023-10-09 19:15:28' union SELECT DATE(authored_date) as Date, authored_date as Time, 'Finish a commit' as Activity, concat(message, ' #', sha) as Details, a.name as Name FROM commits c join _accounts a on c.author_id = a.account_id where authored_date > '2023-10-09 19:15:28' union SELECT DATE(created_date) as Date, created_date as Time, 'Open a PR' as Activity, concat('#', pull_request_key, ' ', title) as Details, a.name as Name FROM pull_requests pr join _accounts a on pr.author_id = a.account_id where created_date > '2023-10-09 19:15:28' union SELECT DATE(merged_date) as Date, merged_date as Time, 'PR gets merged' as Activity, concat('#', pull_request_key, ' ', title) as Details, a.name as Name FROM pull_requests pr join _accounts a on pr.author_id = a.account_id where merged_date > '2023-10-09 19:15:28' union SELECT DATE(prc.created_date) as Date, prc.created_date as Time, 'Comment on PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name FROM pull_request_comments prc left join pull_requests pr on prc.pull_request_id = pr.id join _accounts a on prc.account_id = a.account_id WHERE prc.type = 'NORMAL' and prc.created_date > '2023-10-09 19:15:28' union SELECT DATE(prc.created_date) as Date, prc.created_date as Time, 'Review PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name FROM pull_request_comments prc left join pull_requests pr on prc.pull_request_id = pr.id join _accounts a on prc.account_id = a.account_id WHERE prc.type in ('REVIEW', 'DIFF') and prc.created_date > '2023-10-09 19:15:28' ) t ORDER BY Time desc ), _activity_count_per_day as ( SELECT Date, count(*) as value FROM _activities GROUP BY 1 ), last_few_calendar_months as( -- construct the last few calendar months within the selected time period in the top-right corner SELECT CAST((now()-INTERVAL (H+T+U) DAY) AS date) as d, DATE_FORMAT(CAST((now()-INTERVAL (H+T+U) DAY) AS date), 'w%u %Y') as week_name, DATE_FORMAT(CAST((now()-INTERVAL (H+T+U) DAY) AS date), '%Y%u') as week_number FROM ( SELECT 0 H UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 ) H CROSS JOIN ( SELECT 0 T UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90 ) T CROSS JOIN ( SELECT 0 U UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) U WHERE (now()-INTERVAL (H+T+U) DAY) > '2023-04-09' ), _calendar_months_with_rank as ( SELECT d, concat(week_name, ' (', DATE_FORMAT(DATE_SUB(d, INTERVAL WEEKDAY(d) DAY), '%m/%d'), '~', DATE_FORMAT(DATE_ADD(DATE_SUB(d, INTERVAL WEEKDAY(d) DAY), INTERVAL 6 DAY), '%m/%d'), ')') as week_name, week_number, DATE_FORMAT(d, '%W') as weekday, dense_rank() over(ORDER BY week_number desc) as week_rank FROM last_few_calendar_months ORDER BY 1 desc ), _final_dataset as ( SELECT _calendar_months_with_rank.*, case when _activity_count_per_day.value is null then 0 else _activity_count_per_day.value end as activity_count from _calendar_months_with_rank left join _activity_count_per_day on _calendar_months_with_rank.d = _activity_count_per_day.Date ) SELECT * FROM _final_dataset; ``` This SQL is equivalent to the _final_dataset in the dashboard chart (see the pic below). I just replaced the Grafana variables and methods (E.g. $time_filter(), $time_to, $time_from, and $users) with static values to get it run on Navicat.  ## Run the dynamic SQL to generate the chart I wrote a procedure to dynamically generate a table with the last 54 weeks as the column, Monday, Tuesday, ..., Sunday as the rows. ``` DROP TABLE IF EXISTS WeeklyActivity; SET SESSION group_concat_max_len = 1000000; -- Ensure the group_concat function doesn't truncate the results. -- Step 1: Create the Table with Dynamic Columns SET @columns = ( SELECT GROUP_CONCAT(DISTINCT CONCAT('`', week_name, '` INT DEFAULT 0') ORDER BY week_number DESC SEPARATOR ', ' ) FROM _worklogs ); SET @create_table_sql = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS WeeklyActivity (`Day` VARCHAR(9), ', @columns, ');'); PREPARE create_stmt FROM @create_table_sql; EXECUTE create_stmt; DEALLOCATE PREPARE create_stmt; SELECT @columns; SELECT @create_table_sql; SELECT * FROM WeeklyActivity; -- Step 2: Populate the Table with Aggregate Values for Each Day -- Retrieve the distinct week names from the _worklogs table to generate the column list SELECT GROUP_CONCAT(DISTINCT CONCAT('`', week_name, '`') ORDER BY week_number DESC SEPARATOR ', ') INTO @week_names FROM ( SELECT week_name, week_number FROM _worklogs GROUP BY week_name, week_number ORDER BY week_number DESC LIMIT 54 ) AS subquery_week_names; select @week_names; -- Prepare the dynamic SQL for inserting the weekly activity counts SET @insert_sql = CONCAT( 'INSERT INTO WeeklyActivity (Day, ', @week_names, ') ', 'SELECT weekday, ', (SELECT GROUP_CONCAT( CONCAT('SUM(CASE WHEN week_name = ''', week_name, ''' THEN activity_count ELSE 0 END) AS ', CONCAT('`', week_name, '`')) ORDER BY week_number DESC SEPARATOR ',' ) FROM ( SELECT week_name, week_number FROM _worklogs GROUP BY week_name, week_number ORDER BY week_number DESC LIMIT 54 ) AS subquery), ' FROM _worklogs ', 'WHERE weekday IN (SELECT DISTINCT weekday FROM _worklogs) ', 'GROUP BY weekday ', 'ORDER BY FIELD(weekday, ''Monday'', ''Tuesday'', ''Wednesday'', ''Thursday'', ''Friday'', ''Saturday'', ''Sunday'');' ); SELECT @insert_sql; -- Execute the dynamic SQL statement PREPARE insert_stmt FROM @insert_sql; EXECUTE insert_stmt; DEALLOCATE PREPARE insert_stmt; SELECT * FROM WeeklyActivity ``` The result of the dynamic SQL is  which is the same as the existing chart  ## To Do - [ ] Find the solution to pre-define the dynamic SQL in a procedure - [ ] Make sure that users can call the procedure based on the temp table _final_results in the Work Logs dashboard ### Related issues _No response_ ### Are you willing to submit a PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
