问:
Is there a way to optimize a group by median query?
I wrote a query that finds the median of values for each month. Doing this was difficult enough because MySQL doesn't have a built in median function so I really had to think outside the box with my intermediate SQL skills. But now the problem is that it takes really long (1 or 2 mins sometimes) to run the query. Is there a way to optimize this query? Or perhaps I should write a python script that finds the median and pushes it to the database using a connector?

Here is the query:

```<code>SET @row_num_pos := 0;
SET @median_group_pos := '';
SET @row_num_neg := 0;
SET @median_group_neg := '';

SELECT
    p.month_num AS 'month_num',
    CASE
        WHEN p.month_num = 1 THEN 'Jan'
        WHEN p.month_num = 2 THEN 'Feb'
        WHEN p.month_num = 3 THEN 'Mar'
        WHEN p.month_num = 4 THEN 'Apr'
        WHEN p.month_num = 5 THEN 'May'
        WHEN p.month_num = 6 THEN 'Jun'
        WHEN p.month_num = 7 THEN 'Jul'
        WHEN p.month_num = 8 THEN 'Aug'
        WHEN p.month_num = 9 THEN 'Sep'
        WHEN p.month_num = 10 THEN 'Oct'
        WHEN p.month_num = 11 THEN 'Nov'
        WHEN p.month_num = 12 THEN 'Dec'
    END AS 'Timeline',
    p.ck_pos_median AS 'CK+ Median',
    n.ck_neg_median AS 'CK- Median'
FROM
    (SELECT
        s.median_month_pos AS 'month_num',
            ROUND(AVG(ck_pos), 1) AS 'ck_pos_median'
    FROM
        (SELECT
        @row_num_pos:=CASE
                WHEN @median_group_pos = q.month_num THEN @row_num_pos + 1
                ELSE 1
            END AS 'count_of_group',
            @median_group_pos:=q.month_num AS 'median_month_pos',
            q.month_num,
            q.ck_pos,
            (SELECT
                    COUNT(*)
                FROM
                    Biocept_DB.result_management_report
                WHERE
                    ck_pos IS NOT NULL
                        AND MONTH(order_date) = q.month_num) AS total_month
    FROM
        (SELECT
        MONTH(order_date) AS 'month_num', ck_pos
    FROM
        Biocept_DB.result_management_report
    WHERE
        ck_pos IS NOT NULL
    ORDER BY MONTH(order_date) , ck_pos ASC) AS q) AS s
    WHERE
        s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    GROUP BY s.median_month_pos) AS p
        JOIN
    (SELECT
        s.median_month_neg AS 'month_num',
            ROUND(AVG(ck_neg), 1) AS 'ck_neg_median'
    FROM
        (SELECT
        @row_num_neg:=CASE
                WHEN @median_group_neg = q.month_num THEN @row_num_neg + 1
                ELSE 1
            END AS 'count_of_group',
            @median_group_neg:=q.month_num AS 'median_month_neg',
            q.month_num,
            q.ck_neg,
            (SELECT
                    COUNT(*)
                FROM
                    Biocept_DB.result_management_report
                WHERE
                    ck_neg IS NOT NULL
                        AND MONTH(order_date) = q.month_num) AS total_month
    FROM
        (SELECT
        MONTH(order_date) AS 'month_num', ck_neg
    FROM
        Biocept_DB.result_management_report
    WHERE
        ck_neg IS NOT NULL
    ORDER BY MONTH(order_date) , ck_neg ASC) AS q) AS s
    WHERE
        s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    GROUP BY s.median_month_neg) AS n ON p.month_num = n.month_num
ORDER BY p.month_num;

SET @row_num_pos := NULL;
SET @median_group_pos := NULL;
SET @row_num_neg := NULL;
SET @median_group_neg := NULL;
</code>```

Here is the table it generates:

答:
I have modified your query a little bit. I hope that the calculation is correct. With you sample date the results are the same.

I my environment your query take 6.22 seconds and my only 20 ms. so it looks 300 times faster.

please test my query and let me know if it works for you. is the speed not good we can optimize a little more with virtual columns.

Please don't forget to set group_concat_max_len to a good value for you.

```<code>SET SESSION group_concat_max_len = 1000000;
</code>```

Query

```<code>SELECT  r.Timeline AS `month_number`
        , SUBSTRING_INDEX(SUBSTRING_INDEX( 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec', ',' , r.Timeline ) , ',' , -1)
            AS Timeline
        , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1)+1 ) , ',' , -1) +
            SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1) + 1 - ((r.cnt_ck_pos&1) XOR 1) ) , ',' , -1)
            ) / 2 AS 'ck_pos_median'
        , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1)+1 ) , ',' , -1) +
            SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1) + 1 - ((r.cnt_ck_neg&1) XOR 1) ) , ',' , -1)
            ) / 2 AS 'ck_neg_median'
FROM (
        SELECT  MONTH(`order_date`) AS 'Timeline'
            , SUM(IF(ck_pos is NULL,0,1)) AS cnt_ck_pos
            , GROUP_CONCAT(ck_pos ORDER BY ck_pos) as grp_ck_pos
            , SUM(IF(ck_neg is NULL,0,1)) AS cnt_ck_neg
            , GROUP_CONCAT(ck_neg ORDER BY ck_neg) as grp_ck_neg
        FROM result_management_report
        where (ck_pos is not null or ck_neg is not null) = 1
        GROUP BY Timeline
) r;
</code>```

Table definition

Sample new query

```<code>mysql> SELECT r.Timeline AS `month_number`
    -> , SUBSTRING_INDEX(SUBSTRING_INDEX( 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec', ',' , r.Timeline ) , ',' , -1)
    -> AS Timeline
    -> , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1)+1 ) , ',' , -1) +
    -> SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1) + 1 - ((r.cnt_ck_pos&1) XOR 1) ) , ',' , -1)
    -> ) / 2 AS 'ck_pos_median'
    -> , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1)+1 ) , ',' , -1) +
    -> SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1) + 1 - ((r.cnt_ck_neg&1) XOR 1) ) , ',' , -1)
    -> ) / 2 AS 'ck_neg_median'
    -> FROM (
    -> SELECT MONTH(`order_date`) AS 'Timeline'
    -> , SUM(IF(ck_pos is NULL,0,1)) AS cnt_ck_pos
    -> , GROUP_CONCAT(ck_pos ORDER BY ck_pos) as grp_ck_pos
    -> , SUM(IF(ck_neg is NULL,0,1)) AS cnt_ck_neg
    -> , GROUP_CONCAT(ck_neg ORDER BY ck_neg) as grp_ck_neg
    -> FROM result_management_report
    -> where (ck_pos is not null or ck_neg is not null) = 1
    -> GROUP BY Timeline
    -> ) r;
+--------------+----------+---------------+---------------+
| month_number | Timeline | ck_pos_median | ck_neg_median |
+--------------+----------+---------------+---------------+
|            1 | Jan      |             2 |             2 |
|            2 | Feb      |             2 |             3 |
|            3 | Mar      |             2 |             3 |
|            4 | Apr      |             4 |             4 |
|            5 | May      |             2 |             3 |
|            6 | Jun      |             3 |             3 |
|            7 | Jul      |             4 |             4 |
|            8 | Aug      |             3 |             7 |
|            9 | Sep      |             4 |            12 |
|           10 | Oct      |             5 |             8 |
|           11 | Nov      |             4 |             9 |
|           12 | Dec      |             2 |            12 |
+--------------+----------+---------------+---------------+
12 rows in set (0.02 sec)
</code>```

Sample old query

```<code>mysql>
mysql> SET @row_num_pos := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @median_group_pos := '';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @row_num_neg := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @median_group_neg := '';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT
    ->     p.month_num AS 'month_num',
    ->     CASE
    ->         WHEN p.month_num = 1 THEN 'Jan'
    ->         WHEN p.month_num = 2 THEN 'Feb'
    ->         WHEN p.month_num = 3 THEN 'Mar'
    ->         WHEN p.month_num = 4 THEN 'Apr'
    ->         WHEN p.month_num = 5 THEN 'May'
    ->         WHEN p.month_num = 6 THEN 'Jun'
    ->         WHEN p.month_num = 7 THEN 'Jul'
    ->         WHEN p.month_num = 8 THEN 'Aug'
    ->         WHEN p.month_num = 9 THEN 'Sep'
    ->         WHEN p.month_num = 10 THEN 'Oct'
    ->         WHEN p.month_num = 11 THEN 'Nov'
    ->         WHEN p.month_num = 12 THEN 'Dec'
    ->     END AS 'Timeline',
    ->     p.ck_pos_median AS 'CK+ Median',
    ->     n.ck_neg_median AS 'CK- Median'
    -> FROM
    ->     (SELECT
    ->         s.median_month_pos AS 'month_num',
    ->             ROUND(AVG(ck_pos), 1) AS 'ck_pos_median'
    ->     FROM
    ->         (SELECT
    ->         @row_num_pos:=CASE
    ->                 WHEN @median_group_pos = q.month_num THEN @row_num_pos + 1
    ->                 ELSE 1
    ->             END AS 'count_of_group',
    ->             @median_group_pos:=q.month_num AS 'median_month_pos',
    ->             q.month_num,
    ->             q.ck_pos,
    ->             (SELECT
    ->                     COUNT(*)
    ->                 FROM
    ->                     result_management_report
    ->                 WHERE
    ->                     ck_pos IS NOT NULL
    ->                         AND MONTH(order_date) = q.month_num) AS total_month
    ->     FROM
    ->         (SELECT
    ->         MONTH(order_date) AS 'month_num', ck_pos
    ->     FROM
    ->         result_management_report
    ->     WHERE
    ->         ck_pos IS NOT NULL
    ->     ORDER BY MONTH(order_date) , ck_pos ASC) AS q) AS s
    ->     WHERE
    ->         s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    ->     GROUP BY s.median_month_pos) AS p
    ->         JOIN
    ->     (SELECT
    ->         s.median_month_neg AS 'month_num',
    ->             ROUND(AVG(ck_neg), 1) AS 'ck_neg_median'
    ->     FROM
    ->         (SELECT
    ->         @row_num_neg:=CASE
    ->                 WHEN @median_group_neg = q.month_num THEN @row_num_neg + 1
    ->                 ELSE 1
    ->             END AS 'count_of_group',
    ->             @median_group_neg:=q.month_num AS 'median_month_neg',
    ->             q.month_num,
    ->             q.ck_neg,
    ->             (SELECT
    ->                     COUNT(*)
    ->                 FROM
    ->                     result_management_report
    ->                 WHERE
    ->                     ck_neg IS NOT NULL
    ->                         AND MONTH(order_date) = q.month_num) AS total_month
    ->     FROM
    ->         (SELECT
    ->         MONTH(order_date) AS 'month_num', ck_neg
    ->     FROM
    ->         result_management_report
    ->     WHERE
    ->         ck_neg IS NOT NULL
    ->     ORDER BY MONTH(order_date) , ck_neg ASC) AS q) AS s
    ->     WHERE
    ->         s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    ->     GROUP BY s.median_month_neg) AS n ON p.month_num = n.month_num
    -> ORDER BY p.month_num;
+-----------+----------+------------+------------+
| month_num | Timeline | CK+ Median | CK- Median |
+-----------+----------+------------+------------+
|         1 | Jan      |        2.0 |        2.0 |
|         2 | Feb      |        2.0 |        3.0 |
|         3 | Mar      |        2.0 |        3.0 |
|         4 | Apr      |        4.0 |        4.0 |
|         5 | May      |        2.0 |        3.0 |
|         6 | Jun      |        3.0 |        3.0 |
|         7 | Jul      |        4.0 |        4.0 |
|         8 | Aug      |        3.0 |        7.0 |
|         9 | Sep      |        4.0 |       12.0 |
|        10 | Oct      |        5.0 |        8.0 |
|        11 | Nov      |        4.0 |        9.0 |
|        12 | Dec      |        2.0 |       12.0 |
+-----------+----------+------------+------------+
12 rows in set (6.22 sec)

mysql>
</code>```

Body:
I wrote a query that finds the median of values for each month. Doing this was difficult enough because MySQL doesn't have a built in median function so I really had to think outside the box with my intermediate SQL skills. But now the problem is that it takes really long (1 or 2 mins sometimes) to run the query. Is there a way to optimize this query? Or perhaps I should write a python script that finds the median and pushes it to the database using a connector?

Here is the query:

SET @row_num_pos := 0;
SET @median_group_pos := '';
SET @row_num_neg := 0;
SET @median_group_neg := '';

SELECT
    p.month_num AS 'month_num',
    CASE
        WHEN p.month_num = 1 THEN 'Jan'
        WHEN p.month_num = 2 THEN 'Feb'
        WHEN p.month_num = 3 THEN 'Mar'
        WHEN p.month_num = 4 THEN 'Apr'
        WHEN p.month_num = 5 THEN 'May'
        WHEN p.month_num = 6 THEN 'Jun'
        WHEN p.month_num = 7 THEN 'Jul'
        WHEN p.month_num = 8 THEN 'Aug'
        WHEN p.month_num = 9 THEN 'Sep'
        WHEN p.month_num = 10 THEN 'Oct'
        WHEN p.month_num = 11 THEN 'Nov'
        WHEN p.month_num = 12 THEN 'Dec'
    END AS 'Timeline',
    p.ck_pos_median AS 'CK+ Median',
    n.ck_neg_median AS 'CK- Median'
FROM
    (SELECT
        s.median_month_pos AS 'month_num',
            ROUND(AVG(ck_pos), 1) AS 'ck_pos_median'
    FROM
        (SELECT
        @row_num_pos:=CASE
                WHEN @median_group_pos = q.month_num THEN @row_num_pos + 1
                ELSE 1
            END AS 'count_of_group',
            @median_group_pos:=q.month_num AS 'median_month_pos',
            q.month_num,
            q.ck_pos,
            (SELECT
                    COUNT(*)
                FROM
                    Biocept_DB.result_management_report
                WHERE
                    ck_pos IS NOT NULL
                        AND MONTH(order_date) = q.month_num) AS total_month
    FROM
        (SELECT
        MONTH(order_date) AS 'month_num', ck_pos
    FROM
        Biocept_DB.result_management_report
    WHERE
        ck_pos IS NOT NULL
    ORDER BY MONTH(order_date) , ck_pos ASC) AS q) AS s
    WHERE
        s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    GROUP BY s.median_month_pos) AS p
        JOIN
    (SELECT
        s.median_month_neg AS 'month_num',
            ROUND(AVG(ck_neg), 1) AS 'ck_neg_median'
    FROM
        (SELECT
        @row_num_neg:=CASE
                WHEN @median_group_neg = q.month_num THEN @row_num_neg + 1
                ELSE 1
            END AS 'count_of_group',
            @median_group_neg:=q.month_num AS 'median_month_neg',
            q.month_num,
            q.ck_neg,
            (SELECT
                    COUNT(*)
                FROM
                    Biocept_DB.result_management_report
                WHERE
                    ck_neg IS NOT NULL
                        AND MONTH(order_date) = q.month_num) AS total_month
    FROM
        (SELECT
        MONTH(order_date) AS 'month_num', ck_neg
    FROM
        Biocept_DB.result_management_report
    WHERE
        ck_neg IS NOT NULL
    ORDER BY MONTH(order_date) , ck_neg ASC) AS q) AS s
    WHERE
        s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    GROUP BY s.median_month_neg) AS n ON p.month_num = n.month_num
ORDER BY p.month_num;

SET @row_num_pos := NULL;
SET @median_group_pos := NULL;
SET @row_num_neg := NULL;
SET @median_group_neg := NULL;

Here is the table it generates:

Body:
I have modified your query a little bit. I hope that the calculation is correct. With you sample date the results are the same.

I my environment your query take 6.22 seconds and my only 20 ms. so it looks 300 times faster.

please test my query and let me know if it works for you. is the speed not good we can optimize a little more with virtual columns.

Please don't forget to set group_concat_max_len to a good value for you.

SET SESSION group_concat_max_len = 1000000;

Query

SELECT  r.Timeline AS `month_number`
        , SUBSTRING_INDEX(SUBSTRING_INDEX( 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec', ',' , r.Timeline ) , ',' , -1)
            AS Timeline
        , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1)+1 ) , ',' , -1) +
            SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1) + 1 - ((r.cnt_ck_pos&1) XOR 1) ) , ',' , -1)
            ) / 2 AS 'ck_pos_median'
        , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1)+1 ) , ',' , -1) +
            SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1) + 1 - ((r.cnt_ck_neg&1) XOR 1) ) , ',' , -1)
            ) / 2 AS 'ck_neg_median'
FROM (
        SELECT  MONTH(`order_date`) AS 'Timeline'
            , SUM(IF(ck_pos is NULL,0,1)) AS cnt_ck_pos
            , GROUP_CONCAT(ck_pos ORDER BY ck_pos) as grp_ck_pos
            , SUM(IF(ck_neg is NULL,0,1)) AS cnt_ck_neg
            , GROUP_CONCAT(ck_neg ORDER BY ck_neg) as grp_ck_neg
        FROM result_management_report
        where (ck_pos is not null or ck_neg is not null) = 1
        GROUP BY Timeline
) r;

Table definition

Sample new query

mysql> SELECT r.Timeline AS `month_number`
    -> , SUBSTRING_INDEX(SUBSTRING_INDEX( 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec', ',' , r.Timeline ) , ',' , -1)
    -> AS Timeline
    -> , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1)+1 ) , ',' , -1) +
    -> SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_pos, ',' , (r.cnt_ck_pos>>1) + 1 - ((r.cnt_ck_pos&1) XOR 1) ) , ',' , -1)
    -> ) / 2 AS 'ck_pos_median'
    -> , ( SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1)+1 ) , ',' , -1) +
    -> SUBSTRING_INDEX( SUBSTRING_INDEX(r.grp_ck_neg, ',' , (r.cnt_ck_neg>>1) + 1 - ((r.cnt_ck_neg&1) XOR 1) ) , ',' , -1)
    -> ) / 2 AS 'ck_neg_median'
    -> FROM (
    -> SELECT MONTH(`order_date`) AS 'Timeline'
    -> , SUM(IF(ck_pos is NULL,0,1)) AS cnt_ck_pos
    -> , GROUP_CONCAT(ck_pos ORDER BY ck_pos) as grp_ck_pos
    -> , SUM(IF(ck_neg is NULL,0,1)) AS cnt_ck_neg
    -> , GROUP_CONCAT(ck_neg ORDER BY ck_neg) as grp_ck_neg
    -> FROM result_management_report
    -> where (ck_pos is not null or ck_neg is not null) = 1
    -> GROUP BY Timeline
    -> ) r;
+--------------+----------+---------------+---------------+
| month_number | Timeline | ck_pos_median | ck_neg_median |
+--------------+----------+---------------+---------------+
|            1 | Jan      |             2 |             2 |
|            2 | Feb      |             2 |             3 |
|            3 | Mar      |             2 |             3 |
|            4 | Apr      |             4 |             4 |
|            5 | May      |             2 |             3 |
|            6 | Jun      |             3 |             3 |
|            7 | Jul      |             4 |             4 |
|            8 | Aug      |             3 |             7 |
|            9 | Sep      |             4 |            12 |
|           10 | Oct      |             5 |             8 |
|           11 | Nov      |             4 |             9 |
|           12 | Dec      |             2 |            12 |
+--------------+----------+---------------+---------------+
12 rows in set (0.02 sec)

Sample old query

mysql>
mysql> SET @row_num_pos := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @median_group_pos := '';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @row_num_neg := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @median_group_neg := '';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT
    ->     p.month_num AS 'month_num',
    ->     CASE
    ->         WHEN p.month_num = 1 THEN 'Jan'
    ->         WHEN p.month_num = 2 THEN 'Feb'
    ->         WHEN p.month_num = 3 THEN 'Mar'
    ->         WHEN p.month_num = 4 THEN 'Apr'
    ->         WHEN p.month_num = 5 THEN 'May'
    ->         WHEN p.month_num = 6 THEN 'Jun'
    ->         WHEN p.month_num = 7 THEN 'Jul'
    ->         WHEN p.month_num = 8 THEN 'Aug'
    ->         WHEN p.month_num = 9 THEN 'Sep'
    ->         WHEN p.month_num = 10 THEN 'Oct'
    ->         WHEN p.month_num = 11 THEN 'Nov'
    ->         WHEN p.month_num = 12 THEN 'Dec'
    ->     END AS 'Timeline',
    ->     p.ck_pos_median AS 'CK+ Median',
    ->     n.ck_neg_median AS 'CK- Median'
    -> FROM
    ->     (SELECT
    ->         s.median_month_pos AS 'month_num',
    ->             ROUND(AVG(ck_pos), 1) AS 'ck_pos_median'
    ->     FROM
    ->         (SELECT
    ->         @row_num_pos:=CASE
    ->                 WHEN @median_group_pos = q.month_num THEN @row_num_pos + 1
    ->                 ELSE 1
    ->             END AS 'count_of_group',
    ->             @median_group_pos:=q.month_num AS 'median_month_pos',
    ->             q.month_num,
    ->             q.ck_pos,
    ->             (SELECT
    ->                     COUNT(*)
    ->                 FROM
    ->                     result_management_report
    ->                 WHERE
    ->                     ck_pos IS NOT NULL
    ->                         AND MONTH(order_date) = q.month_num) AS total_month
    ->     FROM
    ->         (SELECT
    ->         MONTH(order_date) AS 'month_num', ck_pos
    ->     FROM
    ->         result_management_report
    ->     WHERE
    ->         ck_pos IS NOT NULL
    ->     ORDER BY MONTH(order_date) , ck_pos ASC) AS q) AS s
    ->     WHERE
    ->         s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    ->     GROUP BY s.median_month_pos) AS p
    ->         JOIN
    ->     (SELECT
    ->         s.median_month_neg AS 'month_num',
    ->             ROUND(AVG(ck_neg), 1) AS 'ck_neg_median'
    ->     FROM
    ->         (SELECT
    ->         @row_num_neg:=CASE
    ->                 WHEN @median_group_neg = q.month_num THEN @row_num_neg + 1
    ->                 ELSE 1
    ->             END AS 'count_of_group',
    ->             @median_group_neg:=q.month_num AS 'median_month_neg',
    ->             q.month_num,
    ->             q.ck_neg,
    ->             (SELECT
    ->                     COUNT(*)
    ->                 FROM
    ->                     result_management_report
    ->                 WHERE
    ->                     ck_neg IS NOT NULL
    ->                         AND MONTH(order_date) = q.month_num) AS total_month
    ->     FROM
    ->         (SELECT
    ->         MONTH(order_date) AS 'month_num', ck_neg
    ->     FROM
    ->         result_management_report
    ->     WHERE
    ->         ck_neg IS NOT NULL
    ->     ORDER BY MONTH(order_date) , ck_neg ASC) AS q) AS s
    ->     WHERE
    ->         s.count_of_group BETWEEN (s.total_month / 2.0) AND (s.total_month / 2.0 + 1)
    ->     GROUP BY s.median_month_neg) AS n ON p.month_num = n.month_num
    -> ORDER BY p.month_num;
+-----------+----------+------------+------------+
| month_num | Timeline | CK+ Median | CK- Median |
+-----------+----------+------------+------------+
|         1 | Jan      |        2.0 |        2.0 |
|         2 | Feb      |        2.0 |        3.0 |
|         3 | Mar      |        2.0 |        3.0 |
|         4 | Apr      |        4.0 |        4.0 |
|         5 | May      |        2.0 |        3.0 |
|         6 | Jun      |        3.0 |        3.0 |
|         7 | Jul      |        4.0 |        4.0 |
|         8 | Aug      |        3.0 |        7.0 |
|         9 | Sep      |        4.0 |       12.0 |
|        10 | Oct      |        5.0 |        8.0 |
|        11 | Nov      |        4.0 |        9.0 |
|        12 | Dec      |        2.0 |       12.0 |
+-----------+----------+------------+------------+
12 rows in set (6.22 sec)

mysql>


来源: stackexchange

问:
how to scrape all data from this table and store it into php array
how to scrape all data from this table and store it into php array </p>

<p>i have tried this code but it pulls data from other tables also and makes data unusable </p>

```<code><?php
error_reporting(0);
$htmlContent = file_get_contents("https://tools.tracemyip.org/search--ip/list:-v-:gTr=1&gNr=50");

    $DOM = new DOMDocument();
    $DOM->loadHTML($htmlContent);

    $Header = $DOM->getElementsByTagName('th');
    $Detail = $DOM->getElementsByTagName('span');

    //#Get header name of the table
    foreach($Header as $NodeHeader)
    {
        $aDataTableHeaderHTML[] = trim($NodeHeader->textContent);
    }
    //print_r($aDataTableHeaderHTML); die();

    //#Get row data/detail table without header name as key
    $i = 0;
    $j = 0;
    foreach($Detail as $sNodeDetail)
    {
        $aDataTableDetailHTML[$j][] = trim($sNodeDetail->textContent);
        $i = $i + 1;
        $j = $i % count($aDataTableHeaderHTML) == 0 ? $j + 1 : $j;
    }
    //print_r($aDataTableDetailHTML); die();

    //#Get row data/detail table with header name as key and outer array index as row number
    for($i = 0; $i < count($aDataTableDetailHTML); $i++)
    {
        for($j = 0; $j < count($aDataTableHeaderHTML); $j++)
        {
            $aTempData[$i][$aDataTableHeaderHTML[$j]] = $aDataTableDetailHTML[$i][$j];
        }
    }
    $aDataTableDetailHTML = $aTempData; unset($aTempData);
    print_r($aDataTableDetailHTML); die();
?>
</code>```

答:
To do the scraping I'd be tempted to use an XPath query rather than trying to use getElementsByTagName etc as it provides much greater flexibility. You notice below that this is processing the url but also listed is a copy of the report used for testing as it is far faster to process repeatedly.

The first row of the resultant array contains the column headers and subsequent rows the cell content

```<code><?php
    $url='https://tools.tracemyip.org/search--ip/list:-v-:gTr=1&gNr=50';
    $url='c:/temp/IP Address List.html';

    $data=array();

    libxml_use_internal_errors( true );
    $dom=new DOMDocument;
    $dom->validateOnParse=false;
    $dom->recover=true;
    $dom->strictErrorChecking=false;
    $dom->loadHTMLFile( $url );
    libxml_clear_errors();

    # create the XPath object
    $xp=new DOMXPath( $dom );

    # get column headers
    $expr='//table[ @class="tbsClass1" ]/thead/tr/th[@class="header"]';
    $col=$xp->query( $expr );
    if( $col && $col->length > 0 ){
        $tmp=array();
        foreach( $col as $node ){
            $tmp[]=$node->textContent;
        }
        $data[]=$tmp;
    }

    #get row data
    $expr='//table[ @class="tbsClass1" ]/tbody/tr';
    $col=$xp->query( $expr );
    if( $col && $col->length > 0 ){
        #iterate over table rows
        foreach( $col as $node ){

            $tmp=array();

            #find table cells
            for( $i=0; $i < $node->childNodes->length; $i++ ){
                $obj=$node->childNodes[$i];
                if( $obj->nodeType==XML_ELEMENT_NODE )$tmp[]=$obj->textContent;
            }
            $data[]=$tmp;
        }
    }

    printf('```%s```',print_r($data,true));
?>
</code>```

The above yields sample response such as:-

```<code>Array
(
    [0] => Array
        (
            [0] => ID
            [1] => IP Address
            [2] => Organization / ISP
            [3] => Country
            [4] => State
            [5] => City
            [6] => Timezone
            [7] => Browser
            [8] => Operating System
            [9] => Bot/spider
        )

    [1] => Array
        (
            [0] => 1
            [1] => 173.249.60.111
            [2] => Contabo GmbH
            [3] => Germany
            [4] => Bavaria
            [5] => Nuremberg
            [6] => Europe/Berlin
            [7] => - - -
            [8] => - - -
            [9] => No
        )
</code>```

In response to your comment - as mentioned in my subsequent comment, but to clarify. When you wish to process the data ( no idea how you want to use the data ) the fact that each element in the output array does not contain the repeated column header is not a problem. The initial array entry contains all the headers so when processing you can easily access the correct header using the index.

```<code>$headers=array_shift( $data );
foreach( $data as $record ){
    foreach( $record as $i => $value )printf('%s=%s<br />',$headers[$i], $value );
}
</code>```

Which yields:

```<code>ID=1
IP Address=173.249.60.111
Organization / ISP=Contabo GmbH
Country=Germany
State=Bavaria
City=Nuremberg
Timezone=Europe/Berlin
Browser=- - -
Operating System=- - -
Bot/spider=No

ID=2
IP Address=185......etc
</code>```

ContentLicense: CC BY-SA 4.0

Body:
how to scrape all data from this table and store it into php array

i have tried this code but it pulls data from other tables also and makes data unusable

<?php
error_reporting(0);
$htmlContent = file_get_contents("https://tools.tracemyip.org/search--ip/list:-v-:gTr=1&gNr=50");

    $DOM = new DOMDocument();
    $DOM->loadHTML($htmlContent);

    $Header = $DOM->getElementsByTagName('th');
    $Detail = $DOM->getElementsByTagName('span');

    //#Get header name of the table
    foreach($Header as $NodeHeader)
    {
        $aDataTableHeaderHTML[] = trim($NodeHeader->textContent);
    }
    //print_r($aDataTableHeaderHTML); die();

    //#Get row data/detail table without header name as key
    $i = 0;
    $j = 0;
    foreach($Detail as $sNodeDetail)
    {
        $aDataTableDetailHTML[$j][] = trim($sNodeDetail->textContent);
        $i = $i + 1;
        $j = $i % count($aDataTableHeaderHTML) == 0 ? $j + 1 : $j;
    }
    //print_r($aDataTableDetailHTML); die();

    //#Get row data/detail table with header name as key and outer array index as row number
    for($i = 0; $i < count($aDataTableDetailHTML); $i++)
    {
        for($j = 0; $j < count($aDataTableHeaderHTML); $j++)
        {
            $aTempData[$i][$aDataTableHeaderHTML[$j]] = $aDataTableDetailHTML[$i][$j];
        }
    }
    $aDataTableDetailHTML = $aTempData; unset($aTempData);
    print_r($aDataTableDetailHTML); die();
?>

Body:
To do the scraping I'd be tempted to use an XPath query rather than trying to use getElementsByTagName etc as it provides much greater flexibility. You notice below that this is processing the url but also listed is a copy of the report used for testing as it is far faster to process repeatedly.

The first row of the resultant array contains the column headers and subsequent rows the cell content

<?php
    $url='https://tools.tracemyip.org/search--ip/list:-v-:gTr=1&gNr=50';
    $url='c:/temp/IP Address List.html';

    $data=array();

    libxml_use_internal_errors( true );
    $dom=new DOMDocument;
    $dom->validateOnParse=false;
    $dom->recover=true;
    $dom->strictErrorChecking=false;
    $dom->loadHTMLFile( $url );
    libxml_clear_errors();

    # create the XPath object
    $xp=new DOMXPath( $dom );

    # get column headers
    $expr='//table[ @class="tbsClass1" ]/thead/tr/th[@class="header"]';
    $col=$xp->query( $expr );
    if( $col && $col->length > 0 ){
        $tmp=array();
        foreach( $col as $node ){
            $tmp[]=$node->textContent;
        }
        $data[]=$tmp;
    }

    #get row data
    $expr='//table[ @class="tbsClass1" ]/tbody/tr';
    $col=$xp->query( $expr );
    if( $col && $col->length > 0 ){
        #iterate over table rows
        foreach( $col as $node ){

            $tmp=array();

            #find table cells
            for( $i=0; $i < $node->childNodes->length; $i++ ){
                $obj=$node->childNodes[$i];
                if( $obj->nodeType==XML_ELEMENT_NODE )$tmp[]=$obj->textContent;
            }
            $data[]=$tmp;
        }
    }

    printf('<pre>%s</pre>',print_r($data,true));
?>

The above yields sample response such as:-

Array
(
    [0] => Array
        (
            [0] => ID
            [1] => IP Address
            [2] => Organization / ISP
            [3] => Country
            [4] => State
            [5] => City
            [6] => Timezone
            [7] => Browser
            [8] => Operating System
            [9] => Bot/spider
        )

    [1] => Array
        (
            [0] => 1
            [1] => 173.249.60.111
            [2] => Contabo GmbH
            [3] => Germany
            [4] => Bavaria
            [5] => Nuremberg
            [6] => Europe/Berlin
            [7] => - - -
            [8] => - - -
            [9] => No
        )

In response to your comment - as mentioned in my subsequent comment, but to clarify. When you wish to process the data ( no idea how you want to use the data ) the fact that each element in the output array does not contain the repeated column header is not a problem. The initial array entry contains all the headers so when processing you can easily access the correct header using the index.

$headers=array_shift( $data );
foreach( $data as $record ){
    foreach( $record as $i => $value )printf('%s=%s<br />',$headers[$i], $value );
}

Which yields:

ID=1
IP Address=173.249.60.111
Organization / ISP=Contabo GmbH
Country=Germany
State=Bavaria
City=Nuremberg
Timezone=Europe/Berlin
Browser=- - -
Operating System=- - -
Bot/spider=No

ID=2
IP Address=185......etc


问:
storing time in PHP
data stored in table1 is with time stamp. syntax used to store time stamp is NOW(). stored time format is in 2011-05-17 00:46:19. my question is how do I access latest data by time and how do I display time say "the data stored 10 minutes ago" or 5 seconds ago or 1 day etc. I am using MySQL and PHP.

答:
```<code>SELECT * FROM table1 WHERE table1.TimePosted >= NOW() - INTERVAL 10 MIN
</code>```

ContentLicense: CC BY-SA 3.0

Title: storing time in PHP


问:
Android: How to adjust Margin/Padding in Preference?
Before I asked to remove the padding of the PreferenceActivity/PreferenceFragment:

Android: How to maximize PreferenceFragment width (or get rid of margin)?

This time I having trouble adjusting margin/padding before Title Text. (See image below).

Does anybody have any idea?

答:
As of 16 Feb 2020,
This space is meant for icon. To get rid of this space use:

JAVA

```<code>    preference.setIconSpaceReserved(false);
</code>```

XML

```<code><androidx.preference.PreferenceScreen
xmlns:app="http://schemas.android.com/apk/res-auto">

<Preference
    ...
    app:iconSpaceReserved="true/false"
    .../>

</androidx.preference.PreferenceScreen>
</code>```

True adds the padding. False removes it.

PS: Dependency used

```<code>implementation 'androidx.preference:preference:1.1.0'
</code>```

Body:
As of 16 Feb 2020,
This space is meant for icon. To get rid of this space use:

JAVA

    preference.setIconSpaceReserved(false);

XML

<androidx.preference.PreferenceScreen
xmlns:app="http://schemas.android.com/apk/res-auto">

<Preference
    ...
    app:iconSpaceReserved="true/false"
    .../>

</androidx.preference.PreferenceScreen>

True adds the padding. False removes it.

PS: Dependency used

implementation 'androidx.preference:preference:1.1.0'


问:
How to generate unique id in Dart
I write websocket chat.
How to generate unique id for user?

now i use this code:

id = new DateTime.now().millisecondsSinceEpoch;
```

is there any more neat solution?

答:
In year 2020 you can do UniqueKey(); which is a built in class:

https://api.flutter.dev/flutter/foundation/UniqueKey-class.html

Note

A key that is only equal to itself.

This cannot be created with a const constructor because that implies that all instantiated keys would be the same instance and therefore not be unique.

Title: How to generate unique id in Dart

Body:
I write websocket chat.
How to generate unique id for user?

now i use this code:

id = new DateTime.now().millisecondsSinceEpoch;

is there any more neat solution?


问:
azure deployment: no package.json file in site/wwwroot
When I try to open my Azure NodeJS React web app in Chrome, I get the following error:

```<code>PATH="$PATH:/home/site/wwwroot" npm start
ENOENT: no such file or directory, open '/home/site/wwwroot/package.json
</code>```

This checks out when I examine the contents of /site/wwwroot in the Azure SSH:

```<code>_del_node_modules
asset-manifest.json
b
favicon.ico
index.html
logo192.png
logo512.png
manifest.json
oryx-appinsightsloader.js
package-lock.json
precache-manifest.6794c2e4defba13a31dcee7cae67859b.js
precache-manifest.705c77ad34ded94d21baca064fa569aa.js
precache-manifest.730dd540546e78da1c3ebb04036e733d.js
precache-manifest.9980bb9186e0ebd2771eff926887f432.js
precache-manifest.9a518a34a27fad9a357b7c78cdfbb7e8.js
precache-manifest.9c65ce33aab3813a0f162cf91b74c0f4.js
precache-manifest.ac183c8385a56a732a8286d5f2d8fa64.js
precache-manifest.b8756a719b957537ff5eb2ede103962d.js
precache-manifest.cbb7b5b3cc0bfbf94c161143ff651d3b.js
precache-manifest.e58827ccfca6115bc9268d09a7361ddd.js
robots.txt
service-worker.js
static
web.config
</code>```

My build pipeline on Azure runs npm install and npm run build from the root directory, which is successful when run in VScode. The pipeline and release are successful. The outer structure of my directory tree in VScode looks like this:

```<code>├── README.md
├── node_modules
├── package-lock.json
├── package.json
├── public
├── schema.sql
├── server
└── src
</code>```

So it seems like the npm run build is running successfully, creating a build folder that populates the /site/wwwroot folder. But Azure is expecting a package.json file. I tried running npm init in the public folder in VScode and also tried adding npm init to the Azure pipeline build tasks, but was getting the same error.

Also, not sure if this is related or not, but I added a web.config file in the public folder. When I look at that file in the Azure SSH, it looks like it can't be read properly:

```<code>/home/site/wwwroot/web.config: line 1: !--: No such file or directory
/home/site/wwwroot/web.config: line 2: $'\r': command not found
/home/site/wwwroot/web.config: line 3: ?xml: No such file or directory
/home/site/wwwroot/web.config: line 4: configuration: No such file or directory
/home/site/wwwroot/web.config: line 5: system.webServer: No such file or directory
/home/site/wwwroot/web.config: line 6: handlers: No such file or directory
/home/site/wwwroot/web.config: line 7: add: No such file or directory
/home/site/wwwroot/web.config: line 8: /handlers: No such file or directory
/home/site/wwwroot/web.config: line 9: rewrite: No such file or directory
/home/site/wwwroot/web.config: line 10: rules: No such file or directory
/home/site/wwwroot/web.config: line 11: rule: No such file or directory
/home/site/wwwroot/web.config: line 12: match: No such file or directory
/home/site/wwwroot/web.config: line 13: action: No such file or directory
/home/site/wwwroot/web.config: line 14: /rule: No such file or directory
/home/site/wwwroot/web.config: line 15: rule: No such file or directory
/home/site/wwwroot/web.config: line 16: match: No such file or directory
/home/site/wwwroot/web.config: line 17: conditions: No such file or directory
/home/site/wwwroot/web.config: line 18: add: No such file or directory
/home/site/wwwroot/web.config: line 19: add: No such file or directory
/home/site/wwwroot/web.config: line 20: add: No such file or directory
/home/site/wwwroot/web.config: line 21: /conditions: No such file or directory
/home/site/wwwroot/web.config: line 22: action: No such file or directory
/home/site/wwwroot/web.config: line 23: /rule: No such file or directory
/home/site/wwwroot/web.config: line 24: /rules: No such file or directory
/home/site/wwwroot/web.config: line 25: /rewrite: No such file or directory
/home/site/wwwroot/web.config: line 26: /system.webServer: No such file or directory
/home/site/wwwroot/web.config: line 27: syntax error near unexpected token `newline'
/home/site/wwwroot/web.config: line 27: `</configuration>'
</code>```

Here's what it looks like in VScode:

```<code><!-- tells azure to rewrite all urls as if they are pointing to root file-->

<?xml version="1.0"?>
<configuration>
    <system.webServer>
        <handlers>
            <add name="iisnode" path="index.js" verb="*" modules="iisnode"/>
        </handlers>
        <rewrite>
            <rules>
                <rule name="DynamicContent">
                    <match url="/*" />
                    <action type="Rewrite" url="index.js"/>
                </rule>
                <rule name="React Routes" stopProcessing="true">
                    <match url=".*" />
                    <conditions logicalGrouping="MatchAll">
                        <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                        <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
                        <add input="{REQUEST_URI}" pattern="^/(api)" negate="true" />
                    </conditions>
                    <action type="Rewrite" url="/" />
                </rule>
            </rules>
        </rewrite>
    </system.webServer>
</configuration>
</code>```

答:
I also encountered this problem (using windows). It was difficult to create the package.json - as it was not appearing after npm init. Preventing me from being able to install npm packages.

It helped me to:

```<code>type NUL > package.json
</code>```

```<code>npm init -f
</code>```

To get a actual valid package.json in place.

My problem was encountered creating a Functions App in the Azure Portal

Body:
When I try to open my Azure NodeJS React web app in Chrome, I get the following error:

PATH="$PATH:/home/site/wwwroot" npm start
ENOENT: no such file or directory, open '/home/site/wwwroot/package.json

This checks out when I examine the contents of /site/wwwroot in the Azure SSH:

_del_node_modules
asset-manifest.json
b
favicon.ico
index.html
logo192.png
logo512.png
manifest.json
oryx-appinsightsloader.js
package-lock.json
precache-manifest.6794c2e4defba13a31dcee7cae67859b.js
precache-manifest.705c77ad34ded94d21baca064fa569aa.js
precache-manifest.730dd540546e78da1c3ebb04036e733d.js
precache-manifest.9980bb9186e0ebd2771eff926887f432.js
precache-manifest.9a518a34a27fad9a357b7c78cdfbb7e8.js
precache-manifest.9c65ce33aab3813a0f162cf91b74c0f4.js
precache-manifest.ac183c8385a56a732a8286d5f2d8fa64.js
precache-manifest.b8756a719b957537ff5eb2ede103962d.js
precache-manifest.cbb7b5b3cc0bfbf94c161143ff651d3b.js
precache-manifest.e58827ccfca6115bc9268d09a7361ddd.js
robots.txt
service-worker.js
static
web.config

My build pipeline on Azure runs npm install and npm run build from the root directory, which is successful when run in VScode. The pipeline and release are successful. The outer structure of my directory tree in VScode looks like this:

├── README.md
├── node_modules
├── package-lock.json
├── package.json
├── public
├── schema.sql
├── server
└── src

So it seems like the npm run build is running successfully, creating a build folder that populates the /site/wwwroot folder. But Azure is expecting a package.json file. I tried running npm init in the public folder in VScode and also tried adding npm init to the Azure pipeline build tasks, but was getting the same error.

Also, not sure if this is related or not, but I added a web.config file in the public folder. When I look at that file in the Azure SSH, it looks like it can't be read properly:

/home/site/wwwroot/web.config: line 1: !--: No such file or directory
/home/site/wwwroot/web.config: line 2: $'\r': command not found
/home/site/wwwroot/web.config: line 3: ?xml: No such file or directory
/home/site/wwwroot/web.config: line 4: configuration: No such file or directory
/home/site/wwwroot/web.config: line 5: system.webServer: No such file or directory
/home/site/wwwroot/web.config: line 6: handlers: No such file or directory
/home/site/wwwroot/web.config: line 7: add: No such file or directory
/home/site/wwwroot/web.config: line 8: /handlers: No such file or directory
/home/site/wwwroot/web.config: line 9: rewrite: No such file or directory
/home/site/wwwroot/web.config: line 10: rules: No such file or directory
/home/site/wwwroot/web.config: line 11: rule: No such file or directory
/home/site/wwwroot/web.config: line 12: match: No such file or directory
/home/site/wwwroot/web.config: line 13: action: No such file or directory
/home/site/wwwroot/web.config: line 14: /rule: No such file or directory
/home/site/wwwroot/web.config: line 15: rule: No such file or directory
/home/site/wwwroot/web.config: line 16: match: No such file or directory
/home/site/wwwroot/web.config: line 17: conditions: No such file or directory
/home/site/wwwroot/web.config: line 18: add: No such file or directory
/home/site/wwwroot/web.config: line 19: add: No such file or directory
/home/site/wwwroot/web.config: line 20: add: No such file or directory
/home/site/wwwroot/web.config: line 21: /conditions: No such file or directory
/home/site/wwwroot/web.config: line 22: action: No such file or directory
/home/site/wwwroot/web.config: line 23: /rule: No such file or directory
/home/site/wwwroot/web.config: line 24: /rules: No such file or directory
/home/site/wwwroot/web.config: line 25: /rewrite: No such file or directory
/home/site/wwwroot/web.config: line 26: /system.webServer: No such file or directory
/home/site/wwwroot/web.config: line 27: syntax error near unexpected token `newline'
/home/site/wwwroot/web.config: line 27: `</configuration>'

Here's what it looks like in VScode:

<?xml version="1.0"?>
<configuration>
    <system.webServer>
        <handlers>
            <add name="iisnode" path="index.js" verb="*" modules="iisnode"/>
        </handlers>
        <rewrite>
            <rules>
                <rule name="DynamicContent">
                    <match url="/*" />
                    <action type="Rewrite" url="index.js"/>
                </rule>
                <rule name="React Routes" stopProcessing="true">
                    <match url=".*" />
                    <conditions logicalGrouping="MatchAll">
                        <add input="{REQUEST_FILENAME}" matchType="IsFile" negate="true" />
                        <add input="{REQUEST_FILENAME}" matchType="IsDirectory" negate="true" />
                        <add input="{REQUEST_URI}" pattern="^/(api)" negate="true" />
                    </conditions>
                    <action type="Rewrite" url="/" />
                </rule>
            </rules>
        </rewrite>
    </system.webServer>
</configuration>

Body:
I also encountered this problem (using windows). It was difficult to create the package.json - as it was not appearing after npm init. Preventing me from being able to install npm packages.

It helped me to:

type NUL > package.json

npm init -f

To get a actual valid package.json in place.

My problem was encountered creating a Functions App in the Azure Portal


问:
Python object persistence over re-execs
Is where a way to persist objects over re-execs of a running script?  If I want a running script to re execute itself to pick up any code changes, (os.exec*) is there a way to persist the objects for access after the re-execution?  I could set environment variables with pickled ascii data, or write that data to a pipe and re-read it after the re-execution, but that seems inelegant or like a hack.  Even if doing that, not all items pickle well. </p>

答:
If you put your code in a module, you can use the reload() standard function to load the new version of the code. Your main module could look like this:

```<code>import mymodule

while mymodule.go():
    reload(mymodule)
</code>```

Whenever you want to reload your module code, return True from go(). When you want to exit, return False.

Title: Python object persistence over re-execs

Body:
If you put your code in a module, you can use the reload() standard function to load the new version of the code. Your main module could look like this:

import mymodule

while mymodule.go():
    reload(mymodule)

Whenever you want to reload your module code, return True from go(). When you want to exit, return False.


问:
How to update php 5.4 to 7.0 via CLI in Windows
I use this command:

```<code>export PATH=/usr/local/php7/bin:$PATH
</code>```

But still showing:

PHP 5.4.45 (cli) (built: Apr 17 2017 15:59:08)
  Copyright (c) 1997-2014 The PHP Group
  Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies
      with the ionCube PHP Loader (enabled) + Intrusion Protection from ioncube24.com (unconfigured) v10.2.4, Copyright (c) 2002-2018, by ionCube Ltd.
      with Zend Guard Loader v3.3, Copyright (c) 1998-2013, by Zend Technologies

I need to fix that, help me.

答:
Are you doing this through a profile file?

you can create a .profile file if it does not exist: touch ~/.profile

edit the .profile file: nano ~/.profile

add this code inside the file to choose the desired php version: export PATH=/opt/php7.0/bin:$PATH

load the .profile file: source ~/.profile

check the version again: php -v

Body:
I use this command:

export PATH=/usr/local/php7/bin:$PATH

But still showing:

PHP 5.4.45 (cli) (built: Apr 17 2017 15:59:08)
  Copyright (c) 1997-2014 The PHP Group
  Zend Engine v2.4.0, Copyright (c) 1998-2014 Zend Technologies
      with the ionCube PHP Loader (enabled) + Intrusion Protection from ioncube24.com (unconfigured) v10.2.4, Copyright (c) 2002-2018, by ionCube Ltd.
      with Zend Guard Loader v3.3, Copyright (c) 1998-2013, by Zend Technologies

I need to fix that, help me.


问:
Swagger C# Enum generation - underlying int values do not match the original enum
I created an enum on my server with integer values set manually rather than the default increment up from 0

```<code>public enum UserType
{
    Anonymous = 0,
    Customer = 10,
    Technician = 21,
    Manager = 25,
    Primary = 30
}
</code>```

My server is running using AspNetCore.App 2.2.0. It's configured in Startup.cs with swashbuckle aspnetcore 4.0.1 to generate a swagger json file to describe the api every time the server is started.

I then use NSwag Studio for windows v 13.2.3.0 to generate a C sharp api client with that swagger JSON file, for use in a Xamarin app. The generated enum in the resulting c sharp api client looks like this - the underlying integer values do not match the original enum.

```<code>[System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.1.5.0 (Newtonsoft.Json v11.0.0.0)")]
public enum UserType
{
    [System.Runtime.Serialization.EnumMember(Value = @"Anonymous")]
    Anonymous = 0,

    [System.Runtime.Serialization.EnumMember(Value = @"Customer")]
    Customer = 1,

    [System.Runtime.Serialization.EnumMember(Value = @"Technician")]
    Technician = 2,

    [System.Runtime.Serialization.EnumMember(Value = @"Manager")]
    Manager = 3,

    [System.Runtime.Serialization.EnumMember(Value = @"Primary")]
    Primary = 4,

}
</code>```

This creates a problem for me client side as there are situations where I need to know the integer value. I am looking for a solution where I can avoid writing converters every time I want to know the integer value on the client side.

Option 1:
Is there an option I am missing in either NSwag Studio or in .net configuration (my Startup.Cs config is below for reference) where I can force the generated enums to get the same integer values as the original enum?

Option 2:
Alternatively if not, both my client and my server have access to the same original enum via a shared class library. Is there a way to get the generated api client to use the actual original enums in the apiclient.cs rather than generate its own?

Reference:

The enums part of my swagger generation code in Startup.Cs looks like this

```<code>services.AddJsonOptions(options =>
{
   options.SerializerSettings.Converters.Add(new StringEnumConverter());
....

services.AddSwaggerGen(setup =>
{
   setup.SwaggerDoc("v1", new Info { Title = AppConst.SwaggerTitle, Version = "v1" });

   setup.UseReferencedDefinitionsForEnums();
   ... other stuff...
 }
</code>```

答:
UPDATE

dawood posted a working solution above that does exactly what I want it to.

ORIGINAL ANSWER

There appears to be no way to do this currently. As @sellotape mentioned in his comment, it may not even be a good idea. Since I'm in control of the server and it's a relatively new project I have refactored my enum to be the normal "sequential from zero" style.

I do think it would be useful for some use-cases - e.g. supporting a legacy enum that can't be refactored easily, or the ability to number the enums with gaps in the middle e.g. 10,20,30. This would enable inserting 11,12 etc at a later time, whilst retaining the ability to encode some kind of "order" to your enum and not break that order as the project grows.

At the moment it doesn't seem possible however, so we'll go with this.

Body:
I created an enum on my server with integer values set manually rather than the default increment up from 0

public enum UserType
{
    Anonymous = 0,
    Customer = 10,
    Technician = 21,
    Manager = 25,
    Primary = 30
}

My server is running using AspNetCore.App 2.2.0. It's configured in Startup.cs with swashbuckle aspnetcore 4.0.1 to generate a swagger json file to describe the api every time the server is started.

I then use NSwag Studio for windows v 13.2.3.0 to generate a C sharp api client with that swagger JSON file, for use in a Xamarin app. The generated enum in the resulting c sharp api client looks like this - the underlying integer values do not match the original enum.

[System.CodeDom.Compiler.GeneratedCode("NJsonSchema", "10.1.5.0 (Newtonsoft.Json v11.0.0.0)")]
public enum UserType
{
    [System.Runtime.Serialization.EnumMember(Value = @"Anonymous")]
    Anonymous = 0,

    [System.Runtime.Serialization.EnumMember(Value = @"Customer")]
    Customer = 1,

    [System.Runtime.Serialization.EnumMember(Value = @"Technician")]
    Technician = 2,

    [System.Runtime.Serialization.EnumMember(Value = @"Manager")]
    Manager = 3,

    [System.Runtime.Serialization.EnumMember(Value = @"Primary")]
    Primary = 4,

}

This creates a problem for me client side as there are situations where I need to know the integer value. I am looking for a solution where I can avoid writing converters every time I want to know the integer value on the client side.

Option 1:
Is there an option I am missing in either NSwag Studio or in .net configuration (my Startup.Cs config is below for reference) where I can force the generated enums to get the same integer values as the original enum?

Option 2:
Alternatively if not, both my client and my server have access to the same original enum via a shared class library. Is there a way to get the generated api client to use the actual original enums in the apiclient.cs rather than generate its own?

Reference:

The enums part of my swagger generation code in Startup.Cs looks like this

services.AddJsonOptions(options =>
{
   options.SerializerSettings.Converters.Add(new StringEnumConverter());
....

services.AddSwaggerGen(setup =>
{
   setup.SwaggerDoc("v1", new Info { Title = AppConst.SwaggerTitle, Version = "v1" });

   setup.UseReferencedDefinitionsForEnums();
   ... other stuff...
 }


问:
Java: Is there a method to convert ArrayList<Boolean> to boolean[] (primitive array)?
I know it can be done by iterating through the elements, but is there a predefined method or a way to use map?</p>

答:
Apache ArrayUtils.toPrimitive

public static boolean[] toPrimitive(Boolean[] array,
                                      boolean valueForNull) Converts an array of object Booleans to primitives handling null.

This method returns null for a null input array.

However, this method requires a Boolean array, so first you need to convert your list to an array using toArray.

```<code>ArrayUtils.toPrimitive(list.toArray(ArrayUtils.EMPTY_BOOLEAN_OBJECT_ARRAY));
</code>```

You will need to download of of the Apache Commons Lang libraries.

https://commons.apache.org/proper/commons-lang/download_lang.cgi

Body:
Apache ArrayUtils.toPrimitive

public static boolean[] toPrimitive(Boolean[] array,
                                      boolean valueForNull) Converts an array of object Booleans to primitives handling null.

This method returns null for a null input array.

However, this method requires a Boolean array, so first you need to convert your list to an array using toArray.

ArrayUtils.toPrimitive(list.toArray(ArrayUtils.EMPTY_BOOLEAN_OBJECT_ARRAY));

You will need to download of of the Apache Commons Lang libraries.

https://commons.apache.org/proper/commons-lang/download_lang.cgi


问:
How to pass object from function to oncreate method in android
Hi I have the following code:

```<code>class MainActivity : AppCompatActivity() {

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        button_search.setOnClickListener {
            val search = editText_user_search.text.toString()
            fetchJson(search)
            val intent = Intent(this, WeatherForecast::class.java)
        }

    }
}

private fun fetchJson(userSearch: String){
    println("Attempting to fetch JSON")

    val url = "https://api.openweathermap.org/data/2.5/weather?q=$userSearch&appid=MY_API_KEY"

    //val url = "https://api.letsbuildthatapp.com/youtube/home_feed"
    println("the url is $url")
    val request = Request.Builder().url(url).build()
    val client = OkHttpClient()

    client.newCall(request).enqueue(object : Callback {
        override fun onResponse(call: Call, response: Response){
            val body = response.body?.string()

            val gson = GsonBuilder().create()

            val weatherinfo: WeatherInfo = gson.fromJson(body, WeatherInfo::class.java)

        }

        override fun onFailure(call: Call, e: IOException) {
            println("Failed to execute request.")
        }
    })
}
</code>```

The idea is to take a user input (a city), do a get request (to get weather info about that city), use gson to turn it into a object then access fields of that object to put it onto a intent to pass to a new activity.

The problem here is that the object I want to access in onCreate, weatherInfo, is created during the function onResponse function inside the fetchJson function.

What is the best way to pass this information so I can add to my intent the following line intent.putStringExtra("City", weatherInfo.name) so I can access it on another activity.

If there is a best practise for handling this situation I would be grateful to know!

答:
You can use suspendCoroutine, see https://kotlinlang.org/api/latest/jvm/stdlib/kotlin.coroutines.experimental/suspend-coroutine.html

```<code>private fun fetchJson(userSearch: String): WeatherInfo? = suspendCoroutine<WeatherInfo?> { cont ->
    println("Attempting to fetch JSON")

    val url = "https://api.openweathermap.org/data/2.5/weather?q=$userSearch&appid=MY_API_KEY"

    //val url = "https://api.letsbuildthatapp.com/youtube/home_feed"
    println("the url is $url")
    val request = Request.Builder().url(url).build()
    val client = OkHttpClient()

    client.newCall(request).enqueue(object : Callback {
        override fun onResponse(call: Call, response: Response){
            val body = response.body?.string()

            val gson = GsonBuilder().create()

            val weatherinfo: WeatherInfo = gson.fromJson(body, WeatherInfo::class.java)

            cont.resume(weatherinfo)
        }

        override fun onFailure(call: Call, e: IOException) {
            println("Failed to execute request.")
            cont.resume(null)
        }
    })
}
</code>```

Now the fetchJson will return WeatherInfo object if call was succeed and null if onFailure() was called

Edit1: You can also use Continuation.resumeWithException(e) instead of resuming with null if you want to handle/propagate exception instead of Nulls.

Edit2: see https://discuss.kotlinlang.org/t/where-is-suspendcoroutine-supposed-to-be-used/11190/2 for more knowledge about this topic.

Body:
Hi I have the following code:

class MainActivity : AppCompatActivity() {

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        button_search.setOnClickListener {
            val search = editText_user_search.text.toString()
            fetchJson(search)
            val intent = Intent(this, WeatherForecast::class.java)
        }

    }
}

private fun fetchJson(userSearch: String){
    println("Attempting to fetch JSON")

    val url = "https://api.openweathermap.org/data/2.5/weather?q=$userSearch&appid=MY_API_KEY"

    //val url = "https://api.letsbuildthatapp.com/youtube/home_feed"
    println("the url is $url")
    val request = Request.Builder().url(url).build()
    val client = OkHttpClient()

    client.newCall(request).enqueue(object : Callback {
        override fun onResponse(call: Call, response: Response){
            val body = response.body?.string()

            val gson = GsonBuilder().create()

            val weatherinfo: WeatherInfo = gson.fromJson(body, WeatherInfo::class.java)

        }

        override fun onFailure(call: Call, e: IOException) {
            println("Failed to execute request.")
        }
    })
}

The idea is to take a user input (a city), do a get request (to get weather info about that city), use gson to turn it into a object then access fields of that object to put it onto a intent to pass to a new activity.

The problem here is that the object I want to access in onCreate, weatherInfo, is created during the function onResponse function inside the fetchJson function.

What is the best way to pass this information so I can add to my intent the following line intent.putStringExtra("City", weatherInfo.name) so I can access it on another activity.

If there is a best practise for handling this situation I would be grateful to know!

Body:
You can use suspendCoroutine, see https://kotlinlang.org/api/latest/jvm/stdlib/kotlin.coroutines.experimental/suspend-coroutine.html

private fun fetchJson(userSearch: String): WeatherInfo? = suspendCoroutine<WeatherInfo?> { cont ->
    println("Attempting to fetch JSON")

    val url = "https://api.openweathermap.org/data/2.5/weather?q=$userSearch&appid=MY_API_KEY"

    //val url = "https://api.letsbuildthatapp.com/youtube/home_feed"
    println("the url is $url")
    val request = Request.Builder().url(url).build()
    val client = OkHttpClient()

    client.newCall(request).enqueue(object : Callback {
        override fun onResponse(call: Call, response: Response){
            val body = response.body?.string()

            val gson = GsonBuilder().create()

            val weatherinfo: WeatherInfo = gson.fromJson(body, WeatherInfo::class.java)

            cont.resume(weatherinfo)
        }

        override fun onFailure(call: Call, e: IOException) {
            println("Failed to execute request.")
            cont.resume(null)
        }
    })
}

Now the fetchJson will return WeatherInfo object if call was succeed and null if onFailure() was called

Edit1: You can also use Continuation.resumeWithException(e) instead of resuming with null if you want to handle/propagate exception instead of Nulls.

Edit2: see https://discuss.kotlinlang.org/t/where-is-suspendcoroutine-supposed-to-be-used/11190/2 for more knowledge about this topic.


问:
Using OR in filters for Office 365 Service Communications API or filtering by multiple IDs
How can I filter by multiple IDs using the Office 365 Service Communications API?

I'm getting the current status using https://manage.office.com/api/v1.0/{tenent}/ServiceComms/CurrentStatus which gives me incident IDs. I'm trying to use those IDs to get the messages affiliated but the message endpoint /ServiceComms/Messages is not working well with the filters.
The documentation shows:

I've tried:

/ServiceComms/Messages?ID=CR555555,/ServiceComms/Messages?ID='CR555555', /ServiceComms/Messages?Id=CR555555,/ServiceComms/Messages?Id='CR555555' - Returns everything (doesn't filter)

/ServiceComms/Messages?$filter=ID eq 'CR555555' - Returns Error

/ServiceComms/Messages?$filter=Id eq 'CR555555' - Returns 1 result

/ServiceComms/Messages?$filter=Id eq 'CR555555' or Id eq 'CR555556' - Returns error

I was able to get an AND result using
$filter=MessageType eq Microsoft.Office365ServiceComms.ExposedContracts.MessageType'Incident' and EndTime ge 2019-07-25T00:00:00Z but OR always errors with the message "Filter {filter} is invalid. Expected '<Item> <operator> <Value>' pattern."

答:
You need to use Microsoft.Office365ServiceComms.ExposedContracts.MessageType'Incident' as your MessageType.

A request to /Messages should return you a odata context at the top of the response. For example:

```<code>{
    "@odata.context": "https://office365servicecomms-prod.cloudapp.net/api/v1.0/{{TenantID}}/$metadata#Messages",
    ...
}

If you query that, you receive the metadata along with the possible filters. I cannot give you an example as of today (2020-02-16) a query returns a 500 Internal Server Error. Microsoft is working on a fix. Maybe it works again by the time you read my reply.
</code>```

Complete example:

https://manage.office.com/api/v1.0/{{TenantID}}/ServiceComms/Messages?$filter=MessageType eq Microsoft.Office365ServiceComms.ExposedContracts.MessageType'Incident' and LastUpdatedTime ge 2020-02-13T00:00:00.000Z

And yes, the ID filter must be written 'Id'. Microsofts documentation has a lot of wrong information in it.

Body:
You need to use Microsoft.Office365ServiceComms.ExposedContracts.MessageType'Incident' as your MessageType.

A request to /Messages should return you a odata context at the top of the response. For example:

{
    "@odata.context": "https://office365servicecomms-prod.cloudapp.net/api/v1.0/{{TenantID}}/$metadata#Messages",
    ...
}

If you query that, you receive the metadata along with the possible filters. I cannot give you an example as of today (2020-02-16) a query returns a 500 Internal Server Error. Microsoft is working on a fix. Maybe it works again by the time you read my reply.

Complete example:

https://manage.office.com/api/v1.0/{{TenantID}}/ServiceComms/Messages?$filter=MessageType eq Microsoft.Office365ServiceComms.ExposedContracts.MessageType'Incident' and LastUpdatedTime ge 2020-02-13T00:00:00.000Z

And yes, the ID filter must be written 'Id'. Microsofts documentation has a lot of wrong information in it.


问:
angular material 2 custom component using mat-auto-complete
I have multiple forms where users have to specify their country through an auto-complete.

I implemented a service performing the auto-completion (contact backend, handle responses and return suggestions)

Now I want to create a component to reuse throughout all the forms.

This component would depend on:

the service I mentioned

a matInput field

a mat-auto-complete.

The component would implement ControlValueAccessor and MatFormFieldControl

Parent components would use it like this

```<code><mat-form-field>
  <auto-completed-country formControlName="country"></auto-completed-country>
</mat-form-field>
</code>```

The parent component creates the FormControl named country and specifies its validators (in some forms the country is required, while in others is not).

Question

Usually matInput is bound to a FormControl, and with an auto-complete through the [matAutocomplete] directive.

However in my scenario this would not be the case, as matInput is not bound to the FormControl, which I think it should be ok, as it seems that one can have a matInput not bound to any FormControl.

However I am worried about the [matAutocomplete] directive, which I figured reading the source that it interacts with matInput and its associated FormControl.

How should I approach this?

Am I making things more complicated than necessary?

答: Auto complete without FormControl will work fine. </p>

Body:
I have multiple forms where users have to specify their country through an auto-complete.

I implemented a service performing the auto-completion (contact backend, handle responses and return suggestions)

Now I want to create a component to reuse throughout all the forms.

This component would depend on:

the service I mentioned

a matInput field

a mat-auto-complete.

The component would implement ControlValueAccessor and MatFormFieldControl

Parent components would use it like this

<mat-form-field>
  <auto-completed-country formControlName="country"></auto-completed-country>
</mat-form-field>

The parent component creates the FormControl named country and specifies its validators (in some forms the country is required, while in others is not).

Question

Usually matInput is bound to a FormControl, and with an auto-complete through the [matAutocomplete] directive.

However in my scenario this would not be the case, as matInput is not bound to the FormControl, which I think it should be ok, as it seems that one can have a matInput not bound to any FormControl.

However I am worried about the [matAutocomplete] directive, which I figured reading the source that it interacts with matInput and its associated FormControl.

How should I approach this?

Am I making things more complicated than necessary?


问:
I'm trying to open firebase website in my app but it requests for external browser
I am trying to open the firebase website inside my app using webview. But the app requests for external browser while opening that activity.

```<code>public class FirebaseActivity extends AppCompatActivity {

    private WebView firebase;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_firebase);
        firebase = findViewById(R.id.firebase);
        this.firebase.getSettings().setDomStorageEnabled(true);
        this.firebase.getSettings().setJavaScriptEnabled(true);
        firebase.loadUrl("https://console.firebase.google.com/");

    }
}
</code>```

答:
Setting WebViewClient should resolve the issue.</p>

```<code>public class FirebaseActivity extends AppCompatActivity {
    private WebView firebase;
    private class MyWebViewClient extends WebViewClient {
          @Override
          public boolean shouldOverrideUrlLoading(WebView view, String url) {
              view.loadUrl(url);
              return true;
          }
    }
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_firebase);
        firebase = findViewById(R.id.firebase);
        firebase.setWebViewClient(new MyWebViewClient());
        firebase.loadUrl("https://console.firebase.google.com/");
        firebase.requestFocus();
    }
}
</code>```

Body:
I am trying to open the firebase website inside my app using webview. But the app requests for external browser while opening that activity.

public class FirebaseActivity extends AppCompatActivity {

    private WebView firebase;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_firebase);
        firebase = findViewById(R.id.firebase);
        this.firebase.getSettings().setDomStorageEnabled(true);
        this.firebase.getSettings().setJavaScriptEnabled(true);
        firebase.loadUrl("https://console.firebase.google.com/");

    }
}

Body:
Setting WebViewClient should resolve the issue.

public class FirebaseActivity extends AppCompatActivity {
    private WebView firebase;
    private class MyWebViewClient extends WebViewClient {
          @Override
          public boolean shouldOverrideUrlLoading(WebView view, String url) {
              view.loadUrl(url);
              return true;
          }
    }
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_firebase);
        firebase = findViewById(R.id.firebase);
        firebase.setWebViewClient(new MyWebViewClient());
        firebase.loadUrl("https://console.firebase.google.com/");
        firebase.requestFocus();
    }
}


问:
How to avoid Error: $ operator is invalid for atomic vectors using precintcon package in R?
I have a dataframe df,with monthly rainfall

```<code>str(df)
'data.frame':   396 obs. of  21 variables:
 $ year         : int  1986 1986 1986 1986 1986 1986 1986 1986 1986 1986 ...
 $ month        : int  1 2 3 4 5 6 7 8 9 10 ...
 $ stn1         : int  2 42 91 267 482 1282 1207 982 705 259 ...
 $ stn2         : int  0 9 23 61 68 151 196 73 172 59 ...
 $ stn3         : int  0 10 34 52 122 258 349 237 140 154 …
</code>```

I want to calculate Rainfall Anomaly Index (rai) for all the station (stn1, stn2, stn3,....).
I used the following code:

```<code>library(precintcon)
rai(df, granularity = "m")
</code>```

However it gives the following error

```<code>Error: $ operator is invalid for atomic vectors
</code>```

But when I use it for only one station it works

```<code>rai(df[1:3], granularity = "m") #calculating rai for one only station
</code>```

How can I calculate rai for all the station at once without any error?

答:
rai function seems to need daily or monthly precipitation series, so 1st two columns are fixed. We can get all the columns which start with "stn1" and use them in rai function.

```<code>cols <- grep('^stn1', names(df))
output <- Reduce(function(x, y) merge(x, y, by = c('year', 'month')),
      lapply(cols, function(x) precintcon::rai(df[c(1:2, x)], granularity = "m")))
</code>```

Body:
I have a dataframe df,with monthly rainfall

str(df)
'data.frame':   396 obs. of  21 variables:
 $ year         : int  1986 1986 1986 1986 1986 1986 1986 1986 1986 1986 ...
 $ month        : int  1 2 3 4 5 6 7 8 9 10 ...
 $ stn1         : int  2 42 91 267 482 1282 1207 982 705 259 ...
 $ stn2         : int  0 9 23 61 68 151 196 73 172 59 ...
 $ stn3         : int  0 10 34 52 122 258 349 237 140 154 …

I want to calculate Rainfall Anomaly Index (rai) for all the station (stn1, stn2, stn3,....).
I used the following code:

library(precintcon)
rai(df, granularity = "m")

However it gives the following error

Error: $ operator is invalid for atomic vectors

But when I use it for only one station it works

rai(df[1:3], granularity = "m") #calculating rai for one only station

How can I calculate rai for all the station at once without any error?

Body:
rai function seems to need daily or monthly precipitation series, so 1st two columns are fixed. We can get all the columns which start with "stn1" and use them in rai function.

cols <- grep('^stn1', names(df))
output <- Reduce(function(x, y) merge(x, y, by = c('year', 'month')),
      lapply(cols, function(x) precintcon::rai(df[c(1:2, x)], granularity = "m")))


问:
What happened to the "Show In Documentation" option when you right click in XCode?
Just got XCode 4, noticed they removed the "Show In Documentation" option when you right-click.  Is there a way to get this back?  Couldn't find it in preferences.  Doesn't seem like they'd take away something so useful.</p>

答:
If you show the "Quick Help" Utilities view (cmd-opt-2) it will display information about the symbol where your cursor is; if it's something in the documentation it will provide links to its declaration, its class/protocol/framework documentation, and other stuff. Here's what it shows for NSString:

Option-clicking on a symbol provides the same information, plus the little "book" icon will link to the documentation:


问:
Difference between passing a ReactElement and a function that returns a ReactElement
Whats the difference between passing a ReactElement as a property:

First case

```<code><RenderParam ReactElement={<Counter />} />

function RenderParam({ ReactElement }) {
  return <div>{ReactElement}</div>;
}
</code>```

and passing a function that returns a ReactElement:

Second case

```<code>const instantiatedCounter = () => <Counter />;

<RenderParam ReactElement={instantiatedCounter} />

function RenderParam({ ReactElement }) {
  return <div> <ReactElement /> </div>
}
</code>```

I see there are differences in the lifecycle:

Every time the parent react element updates, the mount cycle of Counter is executed for the second case:

```<code>ReactElement changed (at RenderParam lifecycle)
component did mount (at Counter)
</code>```

Also the second case lose its state every time the parent component renders.

I dont see whats difference between them. Why first case its able to keep its state?

答:
This is actually rather simple, the real thing here is the timing of the rendering itself.

lets start from the second case, this is actually a design pattern called Render Props, you can read about it here: https://reactjs.org/docs/render-props.html
in this case, the prop contains a function that returns a React Element, which means that It will be evaluated only when you invoke that function and thus it is not always "alive" like in your first case.

First case: when you bind your prop to an Element, it gets evaluated upon the creation of the parent element. which means that as long as the parent element is "alive" the prop element will be alive.

Body:
Whats the difference between passing a ReactElement as a property:

First case

<RenderParam ReactElement={<Counter />} />

function RenderParam({ ReactElement }) {
  return
{ReactElement}
;
}

and passing a function that returns a ReactElement:

Second case

const instantiatedCounter = () => <Counter />;

<RenderParam ReactElement={instantiatedCounter} />

function RenderParam({ ReactElement }) {
  return
 <ReactElement />

}

I see there are differences in the lifecycle:

Every time the parent react element updates, the mount cycle of Counter is executed for the second case:

ReactElement changed (at RenderParam lifecycle)
component did mount (at Counter)

Also the second case lose its state every time the parent component renders.

I dont see whats difference between them. Why first case its able to keep its state?


问:
using cmake with vs2017, cannot find cmake.exe
According to MSDN I should have cmake installed with latest version of vs2017. I have the project type Visual c++ > CMake, so I think i have selected all the goodies I need.

But I dont see cmake.exe anywhere. Is there some extra package I need to install?

答:
Ran into the same issue today. Path to cmake.exe was not added to PATH environment variable. Had to add it manually. Location for Visual Studio 2019 is:</p>

```C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\CommonExtensions\Microsoft\CMake\CMake\bin```

Body:
Ran into the same issue today. Path to cmake.exe was not added to PATH environment variable. Had to add it manually. Location for Visual Studio 2019 is:

C:\Program Files (x86)\Microsoft Visual Studio\2019\BuildTools\Common7\IDE\CommonExtensions\Microsoft\CMake\CMake\bin


问:
Classic Asp cookie expires date does not always get set
I am trying to set cookie with addheader -method in Classic Asp which is the only way of adding among other things HttpOnly and Secure -flags to cookies. All work with the below code - but there is one exception and its is the expiration date/time.

```<code><%
Response.AddHeader "Set-Cookie", "testCookie=2000; path=/;HttpOnly;Secure;expires=" & dateAdd("d", 365, Now()) & ";samesite=Strict;HostOnly"
%>
</code>```

However, it seems to be browser-related issue. In firefox I can see in the Storage tab of developer tools that expiration time is set. But in Chrome it always stays as the default which is the expiration with the end of session. This same issue is with the Edge too.

Has anyone any experience with this issue?

答:
The expected date format is documented here. You need to produce expiration date in that manner.

In Classic ASP, you can use server-side JavaScript to produce such dates easily.

<%
Response.AddHeader "Set-Cookie", "testCookie=2000; path=/;HttpOnly;Secure;expires=" & HTTPDate(DateAdd("d", 365, Now())) & ";samesite=Strict;HostOnly"
%>
```

<p>HTTPDate.asp</p>

<pre class="lang-js prettyprint-override"><code><script language="javascript" runat="server">
function HTTPDate(vbsDate){
    return (new Date(vbsDate)).toGMTString().replace(/UTC/, "GMT");
}
</script>
</code>```

Edit: Pure VBScript solution added.

```<code><%
Function CurrentTZO()
    With CreateObject("WScript.Shell")
        CurrentTZO = .RegRead( _
        "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
    End With
End Function

Function Pad(text)
    Pad = Right("00" & text, 2)
End Function

Function HTTPDate(ByVal localDate)
    localDate = DateAdd("n", CurrentTZO(), localDate)

    ' WeekdayName and MonthName functions relies on locale
    ' need to produce day and month name abbreviations in en-US locale
    Dim locale : locale = SetLocale("en-US")

    Dim out(5)
    out(0) = WeekdayName(Weekday(localDate), True) & ","
    out(1) = Pad(Day(localDate))
    out(2) = MonthName(Month(localDate), True)
    out(3) = Year(localDate)
    out(4) = Join(Array(Pad(Hour(localDate)), Pad(Minute(localDate)), Pad(Second(localDate))), ":")
    out(5) = "GMT"

    SetLocale locale ' set original locale back

    HTTPDate = Join(out, " ")
End Function

Response.AddHeader "Set-Cookie", "testCookie=2000; path=/;HttpOnly;Secure;expires=" & HTTPDate(DateAdd("d", 365, Now())) & ";samesite=Strict;HostOnly"
%>
</code>```

Body:
I am trying to set cookie with addheader -method in Classic Asp which is the only way of adding among other things HttpOnly and Secure -flags to cookies. All work with the below code - but there is one exception and its is the expiration date/time.

<%
Response.AddHeader "Set-Cookie", "testCookie=2000; path=/;HttpOnly;Secure;expires=" & dateAdd("d", 365, Now()) & ";samesite=Strict;HostOnly"
%>

However, it seems to be browser-related issue. In firefox I can see in the Storage tab of developer tools that expiration time is set. But in Chrome it always stays as the default which is the expiration with the end of session. This same issue is with the Edge too.

Has anyone any experience with this issue?

Body:
The expected date format is documented here. You need to produce expiration date in that manner.

In Classic ASP, you can use server-side JavaScript to produce such dates easily.

<!--#include file="HTTPDate.asp"-->
<%
Response.AddHeader "Set-Cookie", "testCookie=2000; path=/;HttpOnly;Secure;expires=" & HTTPDate(DateAdd("d", 365, Now())) & ";samesite=Strict;HostOnly"
%>

HTTPDate.asp

<script language="javascript" runat="server">
function HTTPDate(vbsDate){
    return (new Date(vbsDate)).toGMTString().replace(/UTC/, "GMT");
}
</script>

Edit: Pure VBScript solution added.

<%
Function CurrentTZO()
    With CreateObject("WScript.Shell")
        CurrentTZO = .RegRead( _
        "HKLM\System\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias")
    End With
End Function

Function Pad(text)
    Pad = Right("00" & text, 2)
End Function

Function HTTPDate(ByVal localDate)
    localDate = DateAdd("n", CurrentTZO(), localDate)

    ' WeekdayName and MonthName functions relies on locale
    ' need to produce day and month name abbreviations in en-US locale
    Dim locale : locale = SetLocale("en-US")

    Dim out(5)
    out(0) = WeekdayName(Weekday(localDate), True) & ","
    out(1) = Pad(Day(localDate))
    out(2) = MonthName(Month(localDate), True)
    out(3) = Year(localDate)
    out(4) = Join(Array(Pad(Hour(localDate)), Pad(Minute(localDate)), Pad(Second(localDate))), ":")
    out(5) = "GMT"

    SetLocale locale ' set original locale back

    HTTPDate = Join(out, " ")
End Function

Response.AddHeader "Set-Cookie", "testCookie=2000; path=/;HttpOnly;Secure;expires=" & HTTPDate(DateAdd("d", 365, Now())) & ";samesite=Strict;HostOnly"
%>


问:
What should I know about C# before walking into an interview for a .NET job?
Sure, we all have written tons of C# code. So walking into an interview for a .NET job should be a no-brainer, right? And then you read some stackoverflow posts and it hits you: You don't know squat!

Remember that site with the cram sessions to go through, before taking an exam? I think stackoverflow could be used to make a sorted (voting) list of topics to know about.

Please only put one topic per post.
Please also add some information about the topic. Links, reference material etc.

EDIT: I guess some of you misunderstood the reason for the post - I'm hoping to generate a list of stuff to know about .NET, a sort of cram session that can be reviewed by everyone on the planet to regularly review. This should help us all remember arcane stuff we never really use.

答: They are likely going to ask you questions that are based more on Object Oriented Design and Programming more than questions that are explicitly geared towards C#. So if you can explain abstraction, polymorphism, interfaces, etc. You should be good to go.</p>

OwnerDisplayName: Daren Thomas

ContentLicense: CC BY-SA 2.5

LastEditorDisplayName: Daren Thomas

OwnerDisplayName: TheTXI


问:
Problems with large data transfers across an asynchronous socket connection in Android
I have an Android app that communicates with a server via asynchronous socket connection to transfer somewhat large packets of information (up to 11 Megs). In order to accommodate for this interactions I have placed small sleeps in key points of the data sending and receiving. On WiFi the connection works. Do to the size of the information one read or write doesn't seem to be sufficient but I have created functions to keep reading off the socket until the transfer is finished.

As I said all of this works on WiFi. However, on 3G or edge networks the transfer fails, interrupts midway, and generally is buggered. I have placed numerous checks and sleeps to ensure that information is given the opportunity to pass, however it fails to.

Unfortunately it is difficult to be clearer as to the source of the problem. I have worked for several days trying to clear up this problem as elegantly as possible with no avail.

TLDNR WiFi is able to send data but mobile networks fail. I need to know why and how to fix it.

Thank you.

答:
You probably want a solution that will allow for resuming interrupted data transfers.  Take a look at how to resume an interrupted download - part 2 and https://stackoverflow.com/search?q=[android]+resume+download for examples.

Also, I highly recommend watching Virgil Dobjanschi's Google I/O 2010 presentation on Android REST client applications, in which he describes designing an app for handling such data and network communications. He is the author of the official Twitter app for Android.


问:
Typescript: conflicting namespaces warning on Angular 9 project compilation with ng-packagr
I am unsure what is really to blame for this issue. I think it's Typescript, but it could be ng-packagr or Angular. It only started when I updated to Angular 9.

Here is the message I get on my production build...

```<code>WARNING: Conflicting namespaces: dist/web-apps-shared/esm2015/public_api.js re-exports 'ɵ0' from both dist/web-apps-shared/esm2015/lib/api-applications/reducers.js and dist/web-apps-shared/esm2015/lib/account-codes/reducers.js (will be ignored)
</code>```

Here is one of the sources that is causing this...

```<code>export const selectTotalAccountCodes = createSelector(selectSharedAccountCodeState,
  (state: SharedAccountCodeState) => state.totalItems);
</code>```

The compiler for some reason takes the function parameter and assigns it to a const and then exports it like so...

```<code>const ɵ0 = (state) => state.totalItems;
export const selectTotalAccountCodes = createSelector(selectSharedAccountCodeState, ɵ0);
export { ɵ0 };
</code>```

The question I have is, why does ɵ0 need to be exported? It is only used internally in this file. I am I missing something? Should worry about this? It doesn't seem to be causing an issue when consuming the library that is built with this code.

答:
I've got the same warning while updating to Angular 9, looking on the web for some info/solutions I've also found this Angular issue page https://github.com/angular/angular/issues/33668 ( 11/2019 so 3 months ago ), where they say that is an Ivy's issue, something related to the "export * ".

This is strange since I need to publish to npm and build recommendation says to disable Ivy , so I've disabled it ( angularCompilerOptions.enableIvy false in tsconfig.lib.json ): instead, set enableIvy to true makes the warning disappear.

So I did this try, while keeping enableIvy set to false in tsconfig.lib.json, in the public-api.ts I've modified the "export * " replacing the "*" with all the exported objects, one by one: warning is gone, the library is working.

But I really don't know if this is a good fix or if it is better just to show the warnings..

Body:
I am unsure what is really to blame for this issue. I think it's Typescript, but it could be ng-packagr or Angular. It only started when I updated to Angular 9.

Here is the message I get on my production build...

WARNING: Conflicting namespaces: dist/web-apps-shared/esm2015/public_api.js re-exports 'ɵ0' from both dist/web-apps-shared/esm2015/lib/api-applications/reducers.js and dist/web-apps-shared/esm2015/lib/account-codes/reducers.js (will be ignored)

Here is one of the sources that is causing this...

export const selectTotalAccountCodes = createSelector(selectSharedAccountCodeState,
  (state: SharedAccountCodeState) => state.totalItems);

The compiler for some reason takes the function parameter and assigns it to a const and then exports it like so...

const ɵ0 = (state) => state.totalItems;
export const selectTotalAccountCodes = createSelector(selectSharedAccountCodeState, ɵ0);
export { ɵ0 };

The question I have is, why does ɵ0 need to be exported? It is only used internally in this file. I am I missing something? Should worry about this? It doesn't seem to be causing an issue when consuming the library that is built with this code.


问:
Map some columns of a data frame to other based on two column match pandas
I have two data frame as shown below

df1:

```<code>Sector     Plot    Price     Count
A          1       250       2
A          2       100       1
A          3       250       3
</code>```

df2:

```<code>Sector     Plot    Usage    Type
A          1       R        Land
A          1       R        Land
A          2       C        Villa
A          3       R        Plot
A          3       R        Plot
A          3       R        Plot
</code>```

From the above I would like to add the Usage and Type column from df2 to df1 based on Sector, Plot match.

Expected Output:

```<code>Sector     Plot    Price     Count   Usage    Type
A          1       250       2       R        Land
A          2       100       1       C        Villa
A          3       250       3       R        Plot
</code>```

I tried below code

```<code>df3 = pd.merge(df1, df2, left_on = ['Sector', 'Plot'],
                     right_on = ['Sector', 'Plot'], how = 'inner')
</code>```

答:
Add DataFrame.drop_duplicates because duplicates in second DataFrame:

```<code>df3 = pd.merge(df1,
               df2.drop_duplicates(['Sector', 'Plot']),
               on = ['Sector', 'Plot'])
print (df3)
  Sector  Plot  Price  Count Usage   Type
0      A     1    250      2     R   Land
1      A     2    100      1     C  Villa
2      A     3    250      3     R   Plot
</code>```

Body:
I have two data frame as shown below

df1:

Sector     Plot    Price     Count
A          1       250       2
A          2       100       1
A          3       250       3

df2:

Sector     Plot    Usage    Type
A          1       R        Land
A          1       R        Land
A          2       C        Villa
A          3       R        Plot
A          3       R        Plot
A          3       R        Plot

From the above I would like to add the Usage and Type column from df2 to df1 based on Sector, Plot match.

Expected Output:

Sector     Plot    Price     Count   Usage    Type
A          1       250       2       R        Land
A          2       100       1       C        Villa
A          3       250       3       R        Plot

I tried below code

df3 = pd.merge(df1, df2, left_on = ['Sector', 'Plot'],
                     right_on = ['Sector', 'Plot'], how = 'inner')

Body:
Add DataFrame.drop_duplicates because duplicates in second DataFrame:

df3 = pd.merge(df1,
               df2.drop_duplicates(['Sector', 'Plot']),
               on = ['Sector', 'Plot'])
print (df3)
  Sector  Plot  Price  Count Usage   Type
0      A     1    250      2     R   Land
1      A     2    100      1     C  Villa
2      A     3    250      3     R   Plot


问:
Get the week start date and week end date from week number
I have a query that counts member's wedding dates in the database.

```<code>SELECT
  SUM(NumberOfBrides) AS [Wedding Count]
  , DATEPART( wk, WeddingDate) AS [Week Number]
  , DATEPART( year, WeddingDate) AS [Year]
FROM  MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC
</code>```

How do I work out when the start and end of each week represented in the result set?

```<code>SELECT
  SUM(NumberOfBrides) AS [Wedding Count]
  , DATEPART(wk, WeddingDate) AS [Week Number]
  , DATEPART(year, WeddingDate) AS [Year]
  , ??? AS WeekStart
  , ??? AS WeekEnd
FROM  MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC
</code>```

答:
I have a way other, It is select day Start and day End of Week Current:

```<code>DATEADD(d, -(DATEPART(dw, GETDATE()-2)), GETDATE()) is date time Start
</code>```

and

```<code>DATEADD(day,7-(DATEPART(dw,GETDATE()-1)),GETDATE()) is date time End
</code>```

Body:
I have a query that counts member's wedding dates in the database.

SELECT
  SUM(NumberOfBrides) AS [Wedding Count]
  , DATEPART( wk, WeddingDate) AS [Week Number]
  , DATEPART( year, WeddingDate) AS [Year]
FROM  MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC

How do I work out when the start and end of each week represented in the result set?

SELECT
  SUM(NumberOfBrides) AS [Wedding Count]
  , DATEPART(wk, WeddingDate) AS [Week Number]
  , DATEPART(year, WeddingDate) AS [Year]
  , ??? AS WeekStart
  , ??? AS WeekEnd
FROM  MemberWeddingDates
GROUP BY DATEPART(year, WeddingDate), DATEPART(wk, WeddingDate)
ORDER BY SUM(NumberOfBrides) DESC

Body:
I have a way other, It is select day Start and day End of Week Current:

DATEADD(d, -(DATEPART(dw, GETDATE()-2)), GETDATE()) is date time Start

and

DATEADD(day,7-(DATEPART(dw,GETDATE()-1)),GETDATE()) is date time End


问:
nested loops having linear time complexity?
```<code>public int[] maxSlidingWindow(int[] nums, int k) {
    int n = nums.length;
    if (n == 0) {
        return nums;
    }
    int[] result = new int[n - k + 1];
    LinkedList<Integer> dq = new LinkedList<>();
    for (int i = 0; i < n; i++) {
        if (!dq.isEmpty() && dq.peek() < i - k + 1) {
            dq.poll();
        }
        while (!dq.isEmpty() && nums[i] >= nums[dq.peekLast()]) {
            dq.pollLast();
        }
        dq.offer(i);
        if (i - k + 1 >= 0) {
            result[i - k + 1] = nums[dq.peek()];
        }
    }
    return result;
}
</code>```

As far as i understand the Worst case complexity for this code would be n*k because in the worst case the inner while loop would be executed k times. However the author has said that amortized time Complexity is O(n). How is that ? i don't completely understand ?

答:
Since the inner (while) loop would have a different number of iterations for each iteration of the outer (for) loop, you can't simply bound the number of iterations of that loop by k, since that bound wouldn't be tight enough.

Instead, we can try to compute the total number of operations across all iterations of the inner loop.

The outer loop adds each index exactly once to the queue (in dq.offer(i)).

Each index that was added to the queue, can be removed only once - either by dq.poll() or by dq.pollLast().

Since each iteration of the while loop must remove an element from the queue, all the iterations of the while loop (across all iterations of the outer for loop) are bounded by n (since there cannot be more than n removals). Therefore, all the iterations of the while loop contribute O(n) to the total running time.

Beside the while loop, the other operations inside the outer for loop clearly take constant time, so they cost O(n) time when adding all the iterations of the outer loop.

Hence the total running time is O(n).

Body:
public int[] maxSlidingWindow(int[] nums, int k) {
    int n = nums.length;
    if (n == 0) {
        return nums;
    }
    int[] result = new int[n - k + 1];
    LinkedList<Integer> dq = new LinkedList<>();
    for (int i = 0; i < n; i++) {
        if (!dq.isEmpty() && dq.peek() = nums[dq.peekLast()]) {
            dq.pollLast();
        }
        dq.offer(i);
        if (i - k + 1 >= 0) {
            result[i - k + 1] = nums[dq.peek()];
        }
    }
    return result;
}

As far as i understand the Worst case complexity for this code would be n*k because in the worst case the inner while loop would be executed k times. However the author has said that amortized time Complexity is O(n). How is that ? i don't completely understand ?


问:
AWS cdk Stack deployment order
I'm looking for a way to specify the deployment order of my stacks in my project.
I know I could add dependencies, however, these stacks do not depend on each other and I might want to delete some at a later time.

I have a framework of services that are needed globally for every other stack (e.g. rds). These are being deployed at the very start.

App-Networking

App-GlobalResources

Now I want to add stacks for new customers, these stacks will depend on the 2 global stacks, and on each other, but not on any of the other customer's stacks.

E.g.

App-Customer-A-EC2 (depends on App-Networking)

App-Customer-A-Lambdas (depends on App-Customer-A-EC2)

App-Customer-A-Settings (depends on App-GlobalResources, App-Customer-A-Lambdas)

and

App-Customer-B-EC2 (depends on App-Networking)

App-Customer-B-Lambdas (depends on App-Customer-B-EC2)

App-Customer-B-Settings (depends on App-GlobalResources, App-Customer-B-Lambdas)

I would like all these stacks to be deployed in this order. First the global stacks, then all of customer a, then all of customer b.

However, this is what cdk is doing:

App-Networking

App-GlobalResources

App-Customer-A-EC2

App-Customer-B-EC2

App-Customer-A-Lambdas

App-Customer-B-Lambdas

App-Customer-A-Settings

App-Customer-B-Settings

Which means customer A has to wait until all other customer resources have been generated before he can use the system and so on. As there are no cross dependencies between the customer stacks, they don't have to be deployed in the order cdk does it.

So, what are my options here? Apart from adding dependencies? I thought initially it would be alphabetically ordered by stack name, or maybe by construct path, but it doesn't seem so.

Thank you!

Edit:
I went through the code of the cdk app and found the sorting code. There currently is no way in my case. The type of sorting used by CDK will always result in the observed pattern.

I am now working around by adding dependencies. When deleting stacks that are "in the middle" and have dependencies, I have to destroy them with the -e argument.

答: You could just invoke cdk deploy [stack_name] multiple times to deploy the "necessary" stacks first (e.g. one that deploys RDS or Networking components) and finally invoke cdk deploy * to provision all the remaining stacks.

Title: AWS cdk Stack deployment order