问:
38 Million records - Deleting duplicate rows basis column name and keeping just one
Disclaimer - I've read many fantastic questions & their answers already and have given it a try too. The only issue is that considering the database size, the system gets stuck at "loading" and it just sits there. By looking at the total number of rows, I've seen changes happening but its just not significant when it doesn't give a warning or do it in pieces. I do have a fair idea of tweaking available code and make it work but I'm not a full time/advance developer (yet!)

Question - I've been working on a database that comprises of product information but has duplicate values (was foolish to not make "Product code" column as unique while importing multiple CVS into the database). I need help with removing the duplicate basis the "product code" but want to "keep one" which has maximum information under "specification" column.

Database - MySQL
Total records - 36 Million +
Total columns - Not more than 15 (but are of less relevance)
Problem - Multiple duplicate values basis "product code" but keep one that has maximum number of characters in the "specifications column"

Database details;
Table name - pro

Columns names are as;
productid - VARCHAR,
manPartId - VARCHAR,
specification - TEXT

So far, I've picked the following code from and have given it a try, but the system gets stuck at "loading" and nothing happens. I assume this is because of the huge number of records it has.

Code that I had tried running in the phpMyAdmin "SQL" section is;

```<code>--------------------------------------------
      delete pro
      from pro
      inner join (
         select max(productid) as lastId, manPartId
           from pro
          group by manPartId
         having count(*) > 1) duplic on duplic.manPartId = pro.manPartId
         where pro.productid < duplic.lastId;
--------------------------------------------
</code>```

The above code has been tweaked from the original available at MySQL delete duplicate records but keep latest

Please do help and understand on where am I going wrong. Please also note that I do understand that above code only works around "deleting all but keeping one" and is not "keeping one basis total text available in Specification column".

Many thanks in advance!

EDIT - As per recommendations from aendeerei, I've made some edits to the details.

```<code>-------------------------------------------------------
productid  | manPartId    |  specification
-------------------------------------------------------
1            ABC1           5MP camera, 2500 MaH, Steel body
2            ABC2           2MP camera, Steel body
3            ABC3           5MP, 6500 MaH, Red
4            ABC1           2500 MaH, Steel body
5            ABC2           5MP camera, plastic body
6            ABC4           5MP camera, 2500 MaH, Steel body
7            ABC5           15MP camera, 4500 MaH
8            ABC2           5MP camera
9            ABC3           15MP, 6500 MaH, Blue body
10           ABC5           2500 MaH, Steel body
-------------------------------------------
</code>```

In the above case, I'm looking at removing the duplicates basis manPartId but want to keep one record that has the maximum (characters) in the specification field.

After running the query, I'd like to see the following updated data having unique manPartId with max text under specification column;

```<code>-------------------------------------------------------
productid  | manPartId    |  specification
---------------------------------------------------------------
1            ABC1           5MP camera, 2500 MaH, Steel body
5            ABC2           5MP camera, plastic body
6            ABC4           5MP camera, 2500 MaH, Steel body
7            ABC5           15MP camera, 4500 MaH, Long life
9            ABC3           15MP, 6500 MaH, Blue body
---------------------------------------------------------------
</code>```

Please accept my apologies if its still unclear!

答:
Well, I can't say much here. Just follow the steps (three + an intermediary one) and read my comments carefully. I have chosen a convenient way for you: to run a simple query per step. It can be done also in other way, e.g. using a stored procedure, or many. But it wouldn't be better for you, since your task is a one-occasion process and a very sensible one. It is better to have the control over all operation results.

You asked me in the comments, what should you use as interface for your task. Well, MySQL Workbench is a good one for such operations, but it breaks/freezes a lot. phpmyadmin? Hm... I use for now SequelPRO and I must say, that I really like it. Can it manage your task? I don't know. But for sure I know one which can: the best MySQL software I ever used - and I'll certainly buy it for personal use too - was SQLyog. An extremely powerful, stable and robust application. Especially when you're dealing with duplicates/exports of databases: it never disappoints.

I saw that you have VARCHAR as data type for the column productid. Make it like this:

```<code>`productid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
</code>```

And, if you want to never have duplicates in manPartid column, then create a UNIQUE index on it.

I also recommend you to keep a uniform naming convention. Like:

productId, or product_id, instead of productid

manPartId, or man_part_id, instead of manPartid

And give the name products to the products table.

Now, I structured my answer into two parts: "steps to follow" and "results". For each step I posted the corresponding step results.

Before you begin doing something:

MAKE A BACK-UP OF YOUR DATA!

I wish you good luck!

STEPS TO FOLLOW:

```<code>=================================================================
STEP 1:
=================================================================
Create a new table proTmp with the following columns:
- manPartid: definition identical with pro.manPartid
- maxLenSpec: maximum specification length of each pro.manPartid.
=================================================================

    CREATE TABLE `proTmp` (
      `manPartId` varchar(255) DEFAULT NULL,
      `maxLenSpec` bigint(20) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

===============================================================
STEP 2:
===============================================================
- Truncate table proTmp;
- Get a dataset with all [pro.manPartid, pro.maxLenSpec] pairs;
- Store the dataset into table proTmp.
===============================================================

    TRUNCATE proTmp;
    INSERT INTO proTmp (
        SELECT
            pro.manPartid
            , MAX(LENGTH(pro.specification)) AS maxLenSpec
        FROM pro
        GROUP BY pro.manPartid
    );

=============================================================
INTERMEDIARY STEP - JUST FOR TEST.
IT ONLY DISPLAYS THE RECORDS WHICH WILL BE DELETED IN STEP 3:
=============================================================
Left join tables pro and proTmp and display only the
records with pro.lenSpec = proTmp.maxLenSpec.
- lenSpec: length of pro.specification
=============================================================

a) Get pro.*, pro.lenSpec and proTmp.* columns, ordered by pro.manPartid.
_________________________________________________________________________

    SELECT
        a.*
        , LENGTH(a.specification) as lenSpec
        , b.*
    FROM pro AS a
    LEFT JOIN proTmp AS b ON b.manPartid = a.manPartid
    WHERE LENGTH(a.specification) = b.maxLenSpec
    ORDER BY a.manPartid;

b) Get only pro.productid column, ordered by pro.productid.
___________________________________________________________

    SELECT a.productid
    FROM pro AS a
    LEFT JOIN proTmp AS b ON b.manPartid = a.manPartid
    WHERE LENGTH(a.specification) = b.maxLenSpec
    ORDER BY a.productid;

====================================================================
STEP 3:
====================================================================
Delete all records from pro having pro.lenSpec != proTmp.maxLenSpec.
IMPORTANT: ordered by pro.productid !!!
====================================================================

    DELETE FROM pro
    WHERE
        pro.productid NOT IN (
            SELECT a.productid
            FROM (SELECT * FROM pro AS tmp) AS a
            LEFT JOIN proTmp AS b ON b.manPartid = a.manPartid
            WHERE LENGTH(a.specification) = b.maxLenSpec
            ORDER BY a.productid
        );
</code>```

THE RESULTS OF THE EXECUTED OPERATIONS:

```<code>------------------------------------------------------------------------------------------------------------------
NOTA BENE:
------------------------------------------------------------------------------------------------------------------
NOTICE THAT I ADDED A NEW RECORD INTO TABLE pro, WITH THE productid = 11 & manPartid = "ABC1". ITS specification
COLUMN HAS THE SAME MAXIMUM LENGTH AS THE RECORD WITH THE productid = 1 & manPartid = "ABC1" !!! IN THE END,
AFTER STEP 3, E:G: AFTER DELETION OF DUPLICATES, BOTH RECORDS SHOULD STILL EXIST IN TABLE pro, BECAUSE THEY BOTH
HAVE THE MAXIMUM LENGTH of specification COLUMN. THEREFORE, THERE WILL STILL EXIST SUCH DUPLICATES IN THE TABLE
pro AFTER DELETION. IN ORDER TO DECIDE WHICH ONLY ONE OF THESE DUPLICATES SHOULD REMAIN IN THE TABLE, YOU MUST
THINK ABOUT SOME OTHER CONDITIONS AS THE ONES WE KNOW FROM YOU IN THIS MOMENT. BUT, FIRST THINGS FIRST...
SEE ALSO THE RESULTS AFTER RUNNING STEP 3.
------------------------------------------------------------------------------------------------------------------

=================================================================
CREATION SYNTAX AND CONTENT OF TABLE pro, USED BY ME:
=================================================================

CREATE TABLE `pro` (
  `productid` varchar(255) DEFAULT NULL,
  `manPartId` varchar(255) DEFAULT NULL,
  `specification` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --------------------------------------------------------
    productid   manPartId   specification
    --------------------------------------------------------
    1           ABC1        5MP camera, 2500 MaH, Steel body
    10          ABC5        2500 MaH, Steel body
    2           ABC2        2MP camera, Steel body
    3           ABC3        5MP, 6500 MaH, Red
    4           ABC1        2500 MaH, Steel body
    5           ABC2        5MP camera, plastic body
    6           ABC4        5MP camera, 2500 MaH, Steel body
    7           ABC5        15MP camera, 4500 MaH
    8           ABC2        5MP camera
    9           ABC3        15MP, 6500 MaH, Blue body
    11          ABC1        12345678901234567890123456789012

===============================================================
STEP 1 - RESULTS: Creation of table proTmp
===============================================================

Just the table proTmp was created, without any content.

===============================================================
STEP 2 - RESULTS: Table proTmp content
===============================================================

    ----------------------
    manPartId   maxLenSpec
    ----------------------
    ABC1        32
    ABC2        24
    ABC3        25
    ABC4        32
    ABC5        21

============================================================
INTERMEDIARY STEP RESULTS - JUST FOR TEST.
IT ONLY DISPLAYS THE RECORDS WHICH WILL BE DELETED IN STEP 3
============================================================

a) Get pro.*, pro.lenSpec and proTmp.* columns, ordered by pro.manPartid.
_________________________________________________________________________

    ----------------------------------------------------------------------------------------------
    productid   manPartId   specification                       lenSpec     manPartId   maxLenSpec
    ----------------------------------------------------------------------------------------------
    1           ABC1        5MP camera, 2500 MaH, Steel body    32          ABC1        32
    11          ABC1        12345678901234567890123456789012    32          ABC1        32
    5           ABC2        5MP camera, plastic body            24          ABC2        24
    9           ABC3        15MP, 6500 MaH, Blue body           25          ABC3        25
    6           ABC4        5MP camera, 2500 MaH, Steel body    32          ABC4        32
    7           ABC5        15MP camera, 4500 MaH               21          ABC5        21

b) Get only pro.productid column, ordered by pro.productid.
___________________________________________________________

    ---------
    productid
    ---------
    1
    11
    5
    6
    7
    9

===========================================================================================
STEP 3 - RESULTS: Table pro after deletion of all duplicates by the two conditions
===========================================================================================

From the log after running the DELETE query:
"No errors, 5 rows affected, taking 6.5 ms"

NOTA BENE: NOTICE THAT THERE ARE STILL TWO RECORDS WITH THE manPartid = "ABC1",
BECAUSE THEY BOTH HAD THE SAME MAXIMUM LENGTH OF THE specification COLUMN !!!

    --------------------------------------------------------
    productid   manPartId   specification
    --------------------------------------------------------
    1           ABC1        5MP camera, 2500 MaH, Steel body
    11          ABC1        12345678901234567890123456789012
    5           ABC2        5MP camera, plastic body
    9           ABC3        15MP, 6500 MaH, Blue body
    6           ABC4        5MP camera, 2500 MaH, Steel body
    7           ABC5        15MP camera, 4500 MaH

I hope it all works.
</code>```

EDIT 1:

DELETE ALL RECORDS EXCEPT THE ONES WITH MAXIMAL `productid`:

STEP 1: THIS STEP MUST BE APPLIED. OTHERWISE YOU ARE RECEIVING A FALSE LIST OF THE RECORDS TO DELETE!:

Convert column productid from VARCHAR to:

```<code>`productid` bigint(20) unsigned NOT NULL
</code>```

STEP 2: Run the following DELETE query.

```<code>DELETE FROM pro
WHERE pro.productid NOT IN (
    SELECT max(b.productid) AS maxPartid
    FROM (SELECT * FROM pro AS a) AS b
    GROUP BY b.manPartid
);
</code>```

Body:
Disclaimer - I've read many fantastic questions & their answers already and have given it a try too. The only issue is that considering the database size, the system gets stuck at "loading" and it just sits there. By looking at the total number of rows, I've seen changes happening but its just not significant when it doesn't give a warning or do it in pieces. I do have a fair idea of tweaking available code and make it work but I'm not a full time/advance developer (yet!)

Question - I've been working on a database that comprises of product information but has duplicate values (was foolish to not make "Product code" column as unique while importing multiple CVS into the database). I need help with removing the duplicate basis the "product code" but want to "keep one" which has maximum information under "specification" column.

Database - MySQL
Total records - 36 Million +
Total columns - Not more than 15 (but are of less relevance)
Problem - Multiple duplicate values basis "product code" but keep one that has maximum number of characters in the "specifications column"

Database details;
Table name - pro

Columns names are as;
productid - VARCHAR,
manPartId - VARCHAR,
specification - TEXT

So far, I've picked the following code from and have given it a try, but the system gets stuck at "loading" and nothing happens. I assume this is because of the huge number of records it has.

Code that I had tried running in the phpMyAdmin "SQL" section is;

--------------------------------------------
      delete pro
      from pro
      inner join (
         select max(productid) as lastId, manPartId
           from pro
          group by manPartId
         having count(*) > 1) duplic on duplic.manPartId = pro.manPartId
         where pro.productid < duplic.lastId;
--------------------------------------------

The above code has been tweaked from the original available at MySQL delete duplicate records but keep latest

Please do help and understand on where am I going wrong. Please also note that I do understand that above code only works around "deleting all but keeping one" and is not "keeping one basis total text available in Specification column".

Many thanks in advance!

EDIT - As per recommendations from aendeerei, I've made some edits to the details.

-------------------------------------------------------
productid  | manPartId    |  specification
-------------------------------------------------------
1            ABC1           5MP camera, 2500 MaH, Steel body
2            ABC2           2MP camera, Steel body
3            ABC3           5MP, 6500 MaH, Red
4            ABC1           2500 MaH, Steel body
5            ABC2           5MP camera, plastic body
6            ABC4           5MP camera, 2500 MaH, Steel body
7            ABC5           15MP camera, 4500 MaH
8            ABC2           5MP camera
9            ABC3           15MP, 6500 MaH, Blue body
10           ABC5           2500 MaH, Steel body
-------------------------------------------

In the above case, I'm looking at removing the duplicates basis manPartId but want to keep one record that has the maximum (characters) in the specification field.

After running the query, I'd like to see the following updated data having unique manPartId with max text under specification column;

-------------------------------------------------------
productid  | manPartId    |  specification
---------------------------------------------------------------
1            ABC1           5MP camera, 2500 MaH, Steel body
5            ABC2           5MP camera, plastic body
6            ABC4           5MP camera, 2500 MaH, Steel body
7            ABC5           15MP camera, 4500 MaH, Long life
9            ABC3           15MP, 6500 MaH, Blue body
---------------------------------------------------------------

Please accept my apologies if its still unclear!

Body:
Well, I can't say much here. Just follow the steps (three + an intermediary one) and read my comments carefully. I have chosen a convenient way for you: to run a simple query per step. It can be done also in other way, e.g. using a stored procedure, or many. But it wouldn't be better for you, since your task is a one-occasion process and a very sensible one. It is better to have the control over all operation results.

You asked me in the comments, what should you use as interface for your task. Well, MySQL Workbench is a good one for such operations, but it breaks/freezes a lot. phpmyadmin? Hm... I use for now SequelPRO and I must say, that I really like it. Can it manage your task? I don't know. But for sure I know one which can: the best MySQL software I ever used - and I'll certainly buy it for personal use too - was SQLyog. An extremely powerful, stable and robust application. Especially when you're dealing with duplicates/exports of databases: it never disappoints.

I saw that you have VARCHAR as data type for the column productid. Make it like this:

`productid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)

And, if you want to never have duplicates in manPartid column, then create a UNIQUE index on it.

I also recommend you to keep a uniform naming convention. Like:

productId, or product_id, instead of productid

manPartId, or man_part_id, instead of manPartid

And give the name products to the products table.

Now, I structured my answer into two parts: "steps to follow" and "results". For each step I posted the corresponding step results.

Before you begin doing something:

MAKE A BACK-UP OF YOUR DATA!

I wish you good luck!

STEPS TO FOLLOW:

=================================================================
STEP 1:
=================================================================
Create a new table proTmp with the following columns:
- manPartid: definition identical with pro.manPartid
- maxLenSpec: maximum specification length of each pro.manPartid.
=================================================================

    CREATE TABLE `proTmp` (
      `manPartId` varchar(255) DEFAULT NULL,
      `maxLenSpec` bigint(20) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

===============================================================
STEP 2:
===============================================================
- Truncate table proTmp;
- Get a dataset with all [pro.manPartid, pro.maxLenSpec] pairs;
- Store the dataset into table proTmp.
===============================================================

    TRUNCATE proTmp;
    INSERT INTO proTmp (
        SELECT
            pro.manPartid
            , MAX(LENGTH(pro.specification)) AS maxLenSpec
        FROM pro
        GROUP BY pro.manPartid
    );

=============================================================
INTERMEDIARY STEP - JUST FOR TEST.
IT ONLY DISPLAYS THE RECORDS WHICH WILL BE DELETED IN STEP 3:
=============================================================
Left join tables pro and proTmp and display only the
records with pro.lenSpec = proTmp.maxLenSpec.
- lenSpec: length of pro.specification
=============================================================

a) Get pro.*, pro.lenSpec and proTmp.* columns, ordered by pro.manPartid.
_________________________________________________________________________

    SELECT
        a.*
        , LENGTH(a.specification) as lenSpec
        , b.*
    FROM pro AS a
    LEFT JOIN proTmp AS b ON b.manPartid = a.manPartid
    WHERE LENGTH(a.specification) = b.maxLenSpec
    ORDER BY a.manPartid;

b) Get only pro.productid column, ordered by pro.productid.
___________________________________________________________

    SELECT a.productid
    FROM pro AS a
    LEFT JOIN proTmp AS b ON b.manPartid = a.manPartid
    WHERE LENGTH(a.specification) = b.maxLenSpec
    ORDER BY a.productid;

====================================================================
STEP 3:
====================================================================
Delete all records from pro having pro.lenSpec != proTmp.maxLenSpec.
IMPORTANT: ordered by pro.productid !!!
====================================================================

    DELETE FROM pro
    WHERE
        pro.productid NOT IN (
            SELECT a.productid
            FROM (SELECT * FROM pro AS tmp) AS a
            LEFT JOIN proTmp AS b ON b.manPartid = a.manPartid
            WHERE LENGTH(a.specification) = b.maxLenSpec
            ORDER BY a.productid
        );

THE RESULTS OF THE EXECUTED OPERATIONS:

------------------------------------------------------------------------------------------------------------------
NOTA BENE:
------------------------------------------------------------------------------------------------------------------
NOTICE THAT I ADDED A NEW RECORD INTO TABLE pro, WITH THE productid = 11 & manPartid = "ABC1". ITS specification
COLUMN HAS THE SAME MAXIMUM LENGTH AS THE RECORD WITH THE productid = 1 & manPartid = "ABC1" !!! IN THE END,
AFTER STEP 3, E:G: AFTER DELETION OF DUPLICATES, BOTH RECORDS SHOULD STILL EXIST IN TABLE pro, BECAUSE THEY BOTH
HAVE THE MAXIMUM LENGTH of specification COLUMN. THEREFORE, THERE WILL STILL EXIST SUCH DUPLICATES IN THE TABLE
pro AFTER DELETION. IN ORDER TO DECIDE WHICH ONLY ONE OF THESE DUPLICATES SHOULD REMAIN IN THE TABLE, YOU MUST
THINK ABOUT SOME OTHER CONDITIONS AS THE ONES WE KNOW FROM YOU IN THIS MOMENT. BUT, FIRST THINGS FIRST...
SEE ALSO THE RESULTS AFTER RUNNING STEP 3.
------------------------------------------------------------------------------------------------------------------

=================================================================
CREATION SYNTAX AND CONTENT OF TABLE pro, USED BY ME:
=================================================================

CREATE TABLE `pro` (
  `productid` varchar(255) DEFAULT NULL,
  `manPartId` varchar(255) DEFAULT NULL,
  `specification` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    --------------------------------------------------------
    productid   manPartId   specification
    --------------------------------------------------------
    1           ABC1        5MP camera, 2500 MaH, Steel body
    10          ABC5        2500 MaH, Steel body
    2           ABC2        2MP camera, Steel body
    3           ABC3        5MP, 6500 MaH, Red
    4           ABC1        2500 MaH, Steel body
    5           ABC2        5MP camera, plastic body
    6           ABC4        5MP camera, 2500 MaH, Steel body
    7           ABC5        15MP camera, 4500 MaH
    8           ABC2        5MP camera
    9           ABC3        15MP, 6500 MaH, Blue body
    11          ABC1        12345678901234567890123456789012

===============================================================
STEP 1 - RESULTS: Creation of table proTmp
===============================================================

Just the table proTmp was created, without any content.

===============================================================
STEP 2 - RESULTS: Table proTmp content
===============================================================

    ----------------------
    manPartId   maxLenSpec
    ----------------------
    ABC1        32
    ABC2        24
    ABC3        25
    ABC4        32
    ABC5        21

============================================================
INTERMEDIARY STEP RESULTS - JUST FOR TEST.
IT ONLY DISPLAYS THE RECORDS WHICH WILL BE DELETED IN STEP 3
============================================================

a) Get pro.*, pro.lenSpec and proTmp.* columns, ordered by pro.manPartid.
_________________________________________________________________________

    ----------------------------------------------------------------------------------------------
    productid   manPartId   specification                       lenSpec     manPartId   maxLenSpec
    ----------------------------------------------------------------------------------------------
    1           ABC1        5MP camera, 2500 MaH, Steel body    32          ABC1        32
    11          ABC1        12345678901234567890123456789012    32          ABC1        32
    5           ABC2        5MP camera, plastic body            24          ABC2        24
    9           ABC3        15MP, 6500 MaH, Blue body           25          ABC3        25
    6           ABC4        5MP camera, 2500 MaH, Steel body    32          ABC4        32
    7           ABC5        15MP camera, 4500 MaH               21          ABC5        21

b) Get only pro.productid column, ordered by pro.productid.
___________________________________________________________

    ---------
    productid
    ---------
    1
    11
    5
    6
    7
    9

===========================================================================================
STEP 3 - RESULTS: Table pro after deletion of all duplicates by the two conditions
===========================================================================================

From the log after running the DELETE query:
"No errors, 5 rows affected, taking 6.5 ms"

NOTA BENE: NOTICE THAT THERE ARE STILL TWO RECORDS WITH THE manPartid = "ABC1",
BECAUSE THEY BOTH HAD THE SAME MAXIMUM LENGTH OF THE specification COLUMN !!!

    --------------------------------------------------------
    productid   manPartId   specification
    --------------------------------------------------------
    1           ABC1        5MP camera, 2500 MaH, Steel body
    11          ABC1        12345678901234567890123456789012
    5           ABC2        5MP camera, plastic body
    9           ABC3        15MP, 6500 MaH, Blue body
    6           ABC4        5MP camera, 2500 MaH, Steel body
    7           ABC5        15MP camera, 4500 MaH

I hope it all works.

EDIT 1:

DELETE ALL RECORDS EXCEPT THE ONES WITH MAXIMAL `productid`:

STEP 1: THIS STEP MUST BE APPLIED. OTHERWISE YOU ARE RECEIVING A FALSE LIST OF THE RECORDS TO DELETE!:

Convert column productid from VARCHAR to:

`productid` bigint(20) unsigned NOT NULL

STEP 2: Run the following DELETE query.

DELETE FROM pro
WHERE pro.productid NOT IN (
    SELECT max(b.productid) AS maxPartid
    FROM (SELECT * FROM pro AS a) AS b
    GROUP BY b.manPartid
);


来源: stackexchange

问:
Subquery optimization
I was reading through this article - NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL at EXPLAIN EXTENDED; I am working with an existing design.

What I want is to be able to select all id's from table a, which are not in table b.

I am using this query:

```<code>SELECT a.* FROM `orders` a LEFT JOIN `orders_corrected` b ON
a.`order_id`=b.`order_id` WHERE b.`order_id` IS NULL;
</code>```

Table a contains over 900K records, and Table b contains over 200K and growing.

The above query takes about 7-8 minutes.

I also tried using NOT IN, which is slow as well.

order_id column is NOT NULL and UNIQUE indexed.

Output of explain:

```<code>*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 595783
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY,order_id_UNIQUE,ix_order_id
key: PRIMARY
key_len: 152
ref: func
rows: 1
Extra: Using where; Not exists; Using index
</code>```

Any help would be great.

答:
What I want is to be able to select all id's from table a, which are not in table b

Use a correlated subquery in a WHERE clause involving an indexed column. For example:

```<code>SELECT U.Id
FROM Users U
WHERE (SELECT Count(Ph.UserId) FROM PostHistory Ph WHERE Ph.UserId = U.Id) = 0
</code>```

References

Users with no Posts - Stack Exchange Data Explorer

MySQL :: MySQL 5.7 Reference Manual :: 13.2.10.7 Correlated Subqueries

MySQL :: MySQL 5.7 Reference Manual :: 13.2.10.10 Optimizing Subqueries

ContentLicense: CC BY-SA 3.0

Title: Subquery optimization

Body:
I was reading through this article - NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL at EXPLAIN EXTENDED; I am working with an existing design.

What I want is to be able to select all id's from table a, which are not in table b.

I am using this query:

SELECT a.* FROM `orders` a LEFT JOIN `orders_corrected` b ON
a.`order_id`=b.`order_id` WHERE b.`order_id` IS NULL;

Table a contains over 900K records, and Table b contains over 200K and growing.

The above query takes about 7-8 minutes.

I also tried using NOT IN, which is slow as well.

order_id column is NOT NULL and UNIQUE indexed.

Output of explain:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 595783
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY,order_id_UNIQUE,ix_order_id
key: PRIMARY
key_len: 152
ref: func
rows: 1
Extra: Using where; Not exists; Using index

Any help would be great.

Body:
What I want is to be able to select all id's from table a, which are not in table b

Use a correlated subquery in a WHERE clause involving an indexed column. For example:

SELECT U.Id
FROM Users U
WHERE (SELECT Count(Ph.UserId) FROM PostHistory Ph WHERE Ph.UserId = U.Id) = 0

References

Users with no Posts - Stack Exchange Data Explorer

MySQL :: MySQL 5.7 Reference Manual :: 13.2.10.7 Correlated Subqueries

MySQL :: MySQL 5.7 Reference Manual :: 13.2.10.10 Optimizing Subqueries


问:
Adding an event handler when a subcontrol is instantiated
I've got a custom FlowLayoutPanel: an "AlbumFlowLayout"  which inherits from a FlowLayoutPanel and is used to hold a collection of UserControls ("AlbumItems"). Typically, this would reside on a form ("FrmMain")so that the hierarchy of items is:

```<code>Form ("FrmMain")
  AlbumFlowLayout ("AlbumFlowLayout1")
    AlbumItems (1 or more)
</code>```

[Is there a way/what is the protocol] for adding a "WasClicked" handler to a created AlbumItem whenever it's created/added to the AlbumFlowLayout?

Ideally I'd like to encapsulate the handler construction code inside the AlbumFlowLayout so that it happens automatically whenever the code in FrmMain does an AlbumFlowLayout.Controls.Add of a new AlbumItem, rather than having a second line in the FrmMain add the handler before adding the control, e.g.:

```<code>Dim myItem As New AlbumItem

AddHandler myItem.WasClicked, AddressOf AlbumFlowLayout1.AlbumItem_WasClicked
AlbumFlowLayout1.Controls.Add(myItem)
</code>```

Thanks!
-Pete

答:
Plutonix had the solution. Here's what the final code looks like to use it:</p>

```<code>Partial Public Class AlbumFlowLayout
  Inherits FlowLayoutPanel

  ' A FlowLayoutPanel for handling collections of AlbumItems

  Public SelectedItems As New List(Of String)
  ' Holds IDs of currently selected items

  Private Sub AlbumFlowLayout_ControlAdded(sender As Object, e As ControlEventArgs) Handles Me.ControlAdded
    ' Wire up each user item as it's added so that it will pass its
    ' Wasclicked up to here

    Dim myAlbumItem As AlbumItem  = e.Control
    AddHandler myAlbumItem.WasClicked, AddressOf Me.AlbumItem_WasClicked
  End Sub

  ' Other methods...
  ' ...

  Public Sub AlbumItem_WasClicked(ByVal sender As Object, e As EventArgs)
    ' Deselects all previously selected items. Doing this via a List to
    ' Allow for expansion item where we permit multi-selection via
    ' Control-key or the like; currently is single-select

    Dim myItem As AlbumItem = sender

    For Each itm As String In SelectedItems
        If itm <> myItem.Name Then
            DirectCast(Me.Controls(itm), AlbumItem).IsSelected = False
        End If
    Next

    SelectedItems.Clear()
    SelectedItems.Add(myItem.Name)
  End Sub
End Class
</code>```

Body:
I've got a custom FlowLayoutPanel: an "AlbumFlowLayout"  which inherits from a FlowLayoutPanel and is used to hold a collection of UserControls ("AlbumItems"). Typically, this would reside on a form ("FrmMain")so that the hierarchy of items is:

Form ("FrmMain")
  AlbumFlowLayout ("AlbumFlowLayout1")
    AlbumItems (1 or more)

[Is there a way/what is the protocol] for adding a "WasClicked" handler to a created AlbumItem whenever it's created/added to the AlbumFlowLayout?

Ideally I'd like to encapsulate the handler construction code inside the AlbumFlowLayout so that it happens automatically whenever the code in FrmMain does an AlbumFlowLayout.Controls.Add of a new AlbumItem, rather than having a second line in the FrmMain add the handler before adding the control, e.g.:

Dim myItem As New AlbumItem

AddHandler myItem.WasClicked, AddressOf AlbumFlowLayout1.AlbumItem_WasClicked
AlbumFlowLayout1.Controls.Add(myItem)

Thanks!
-Pete

Body:
Plutonix had the solution. Here's what the final code looks like to use it:

Partial Public Class AlbumFlowLayout
  Inherits FlowLayoutPanel

  ' A FlowLayoutPanel for handling collections of AlbumItems

  Public SelectedItems As New List(Of String)
  ' Holds IDs of currently selected items

  Private Sub AlbumFlowLayout_ControlAdded(sender As Object, e As ControlEventArgs) Handles Me.ControlAdded
    ' Wire up each user item as it's added so that it will pass its
    ' Wasclicked up to here

    Dim myAlbumItem As AlbumItem  = e.Control
    AddHandler myAlbumItem.WasClicked, AddressOf Me.AlbumItem_WasClicked
  End Sub

  ' Other methods...
  ' ...

  Public Sub AlbumItem_WasClicked(ByVal sender As Object, e As EventArgs)
    ' Deselects all previously selected items. Doing this via a List to
    ' Allow for expansion item where we permit multi-selection via
    ' Control-key or the like; currently is single-select

    Dim myItem As AlbumItem = sender

    For Each itm As String In SelectedItems
        If itm <> myItem.Name Then
            DirectCast(Me.Controls(itm), AlbumItem).IsSelected = False
        End If
    Next

    SelectedItems.Clear()
    SelectedItems.Add(myItem.Name)
  End Sub
End Class


问:
Kotlin: For-loop range must have an 'iterator()' method
If I use var charSet = 1..10 it works, but when I am using the below code, I'm getting this error.

```<code>var charSet = "A".."Z"
for (value in charSet) {
  println("$value")
}
</code>```

For-loop range must have an 'iterator()' method.

Please let me know how to resolve this.

答: charSet is a ClosedRange<String> and that type doesn't have iterator() function (but ex. IntRange does). You can either change your range to 'A'..'Z' or create an extension function ClosedRange<String>.iterator() which would return a Iterator. The error should then go away

Body:
If I use var charSet = 1..10 it works, but when I am using the below code, I'm getting this error.

var charSet = "A".."Z"
for (value in charSet) {
  println("$value")
}

For-loop range must have an 'iterator()' method.

Please let me know how to resolve this.


问:
rxJs & angular 4 & restangular: stack errorInterceptors
In my angular 4 app I'm using ngx-restangular to handle all server calls. It returns observable as result, and this module has hooks to handle errors (like 401 etc).

But from documentation, i can handle 403 (401) so:

```<code>  RestangularProvider.addErrorInterceptor((response, subject, responseHandler) => {
    if (response.status === 403) {

      refreshAccesstoken()
      .switchMap(refreshAccesstokenResponse => {
        //If you want to change request or make with it some actions and give the request to the repeatRequest func.
        //Or you can live it empty and request will be the same.

        // update Authorization header
        response.request.headers.set('Authorization', 'Bearer ' + refreshAccesstokenResponse)

        return response.repeatRequest(response.request);
      })
      .subscribe(
        res => responseHandler(res),
        err => subject.error(err)
      );

      return false; // error handled
    }
    return true; // error not handled
  });
</code>```

and this is good for one request, which has broken with 403 error.
how can i stack this calls using rxJs? Becouse now, for example, i have 3 requests, which have 403 and for each this broken request I'm refreshing token - this is not so good, i have to update my token and then repeat all my broken requests. How can I achive this using Observables?

In angular 1 it was pretty easy:

```<code>Restangular.setErrorInterceptor(function (response, deferred, responseHandler) {
  if (response.status == 403) {
    // send only one request if multiple errors exist
    if (!refreshingIsInProgress) {
      refreshingIsInProgress = AppApi.refreshAccessToken(); // Returns promise
    }

    $q.when(refreshingIsInProgress, function () {
      refreshingIsInProgress = null;

      setHeaders(response.config.headers);

      // repeat request with error
      $http(response.config).then(responseHandler, deferred);
    }, function () {
      refreshingIsInProgress = null;

      $state.go('auth');
    });

    return false; // stop the promise chain
  }

  return true;
});
</code>```

And all was working like a charm. But I'm new to rxJs & angular 4 and I don't have any idea how to achive this with observables and angular 4. Maybe somebody have an idea?

upd!
here is my refreshAccesstoken method

```<code>const refreshAccesstoken = function () {
  const refreshToken = http.post(environment.apiURL + `/token/refresh`,
    {refreshToken: 'someToken'});
  return refreshToken;
};
</code>```

答:
I had a similar problem in my application and I solved it this way:

Subscribers keep waiting for source to emit a value and dont do anything till then

We can use this behavior to solve our problem.

What we can do is something like this :

All HTTP Calls :

```<code>public doSomeHttpCall(params){
   return authService
             .getToken()
             .switchMap(token => httpcall())
}
</code>```

With above code, the HTTP call wont happen until we get the token.

Inside auth service :

```<code>private token = new BehaviorSubject(null);
public getToken(){
   return this.token
              .filter( t => !!t );
}
</code>```

Since I have a filter for token, if token is falsy no HTTP call will be executed. As soon as token gets a good value all the HTTP calls continue to execute.

In your case, if there is a 403 , disable the token by setting it to null. After refresh set it to new token value.

```<code>private refreshInProgress= false;
public refreshToken(){
   if(refreshInProgress){
      //dont do anything
      return;
   }
   refreshInProgress = true;
   this.token.next(null);
   // fetch new token
   this.token.next(newToken);
   refreshInProgress = false;
}
</code>```

A very simple illustration here :

jsbin : https://jsbin.com/mefepipiqu/edit?html,js,console,output

Body:
In my angular 4 app I'm using ngx-restangular to handle all server calls. It returns observable as result, and this module has hooks to handle errors (like 401 etc).

But from documentation, i can handle 403 (401) so:

  RestangularProvider.addErrorInterceptor((response, subject, responseHandler) => {
    if (response.status === 403) {

      refreshAccesstoken()
      .switchMap(refreshAccesstokenResponse => {
        //If you want to change request or make with it some actions and give the request to the repeatRequest func.
        //Or you can live it empty and request will be the same.

        // update Authorization header
        response.request.headers.set('Authorization', 'Bearer ' + refreshAccesstokenResponse)

        return response.repeatRequest(response.request);
      })
      .subscribe(
        res => responseHandler(res),
        err => subject.error(err)
      );

      return false; // error handled
    }
    return true; // error not handled
  });

and this is good for one request, which has broken with 403 error.
how can i stack this calls using rxJs? Becouse now, for example, i have 3 requests, which have 403 and for each this broken request I'm refreshing token - this is not so good, i have to update my token and then repeat all my broken requests. How can I achive this using Observables?

In angular 1 it was pretty easy:

Restangular.setErrorInterceptor(function (response, deferred, responseHandler) {
  if (response.status == 403) {
    // send only one request if multiple errors exist
    if (!refreshingIsInProgress) {
      refreshingIsInProgress = AppApi.refreshAccessToken(); // Returns promise
    }

    $q.when(refreshingIsInProgress, function () {
      refreshingIsInProgress = null;

      setHeaders(response.config.headers);

      // repeat request with error
      $http(response.config).then(responseHandler, deferred);
    }, function () {
      refreshingIsInProgress = null;

      $state.go('auth');
    });

    return false; // stop the promise chain
  }

  return true;
});

And all was working like a charm. But I'm new to rxJs & angular 4 and I don't have any idea how to achive this with observables and angular 4. Maybe somebody have an idea?

upd!
here is my refreshAccesstoken method

const refreshAccesstoken = function () {
  const refreshToken = http.post(environment.apiURL + `/token/refresh`,
    {refreshToken: 'someToken'});
  return refreshToken;
};

Body:
I had a similar problem in my application and I solved it this way:

Subscribers keep waiting for source to emit a value and dont do anything till then

We can use this behavior to solve our problem.

What we can do is something like this :

All HTTP Calls :

public doSomeHttpCall(params){
   return authService
             .getToken()
             .switchMap(token => httpcall())
}

With above code, the HTTP call wont happen until we get the token.

Inside auth service :

private token = new BehaviorSubject(null);
public getToken(){
   return this.token
              .filter( t => !!t );
}

Since I have a filter for token, if token is falsy no HTTP call will be executed. As soon as token gets a good value all the HTTP calls continue to execute.

In your case, if there is a 403 , disable the token by setting it to null. After refresh set it to new token value.

private refreshInProgress= false;
public refreshToken(){
   if(refreshInProgress){
      //dont do anything
      return;
   }
   refreshInProgress = true;
   this.token.next(null);
   // fetch new token
   this.token.next(newToken);
   refreshInProgress = false;
}

A very simple illustration here :

jsbin : https://jsbin.com/mefepipiqu/edit?html,js,console,output


问:
Create Task Schedule that run task after one is finished
Currently I have a task that run every 5 minute.
what I want is to have that task rerun every time it is completed with 1 minute delay.

what I have in mind is to create multiple task, task A and task B. task B will run after task A complete and vice versa. But not sure how to execute that.

答: I have found a workaround for my situation. what I do is create loop for task A to run followed by task B with delay in between. </p>


问:
SAS drop records in by group with only one observation
I have a dataset and I am reading it with a by group statement:

```<code>  data TEMPDATA;
    SET RAWDATA; by SYMBOL DATE;
  run;
  proc expand data=TEMPDATA out=GAPDATA to=day method=step;
  by symbol date;
  id time;
  run;
</code>```

However, I realized that the proc expand procedure would return an error if there is a record in the by group that has only one observation.

For example:

```<code>| Symbol | Date     | Time | BB | BO | MIDPRICE |
|--------|----------|------|----|----|----------|
| AAPL   | 20130102 | 2    | 2  | 3  | 2.5      |
</code>```

If there is only one record of AAPL, SAS will refuse to execute the command.

Therefore, I was wondering if there is a way to drop all the records, with the same symbol, that has only one record in the by group (symbol, date)?

答:
One nice feature in SAS PROC SQL is that you can group by and add summary measures while retaining all detail.  This makes such removal easy (and can be useful in many other contexts as well).  I.e.

```<code>PROC SQL;

CREATE TABLE tempdata2 AS
SELECT *
FROM tempdata
GROUP BY symbol, date
HAVING count(*) > 1
;

QUIT;
</code>```

Body:
I have a dataset and I am reading it with a by group statement:

  data TEMPDATA;
    SET RAWDATA; by SYMBOL DATE;
  run;
  proc expand data=TEMPDATA out=GAPDATA to=day method=step;
  by symbol date;
  id time;
  run;

However, I realized that the proc expand procedure would return an error if there is a record in the by group that has only one observation.

For example:

| Symbol | Date     | Time | BB | BO | MIDPRICE |
|--------|----------|------|----|----|----------|
| AAPL   | 20130102 | 2    | 2  | 3  | 2.5      |

If there is only one record of AAPL, SAS will refuse to execute the command.

Therefore, I was wondering if there is a way to drop all the records, with the same symbol, that has only one record in the by group (symbol, date)?

Body:
One nice feature in SAS PROC SQL is that you can group by and add summary measures while retaining all detail.  This makes such removal easy (and can be useful in many other contexts as well).  I.e.

PROC SQL;

CREATE TABLE tempdata2 AS
SELECT *
FROM tempdata
GROUP BY symbol, date
HAVING count(*) > 1
;

QUIT;


问:
Laravel composer signal "11" error symfony osx
Anything I try to execute in Laravel, even minor files locally gives this message in composer:

[Symfony\Component\Process\Exception\RuntimeException]

  The process has been signaled with signal "11".

Using OSX 10.7.5

Any help appreciated, as always.

For example :

```<code>composer update
</code>```

typed in terminal retrieves the error message above

答:
Try to run thin command</p>
```<code>composer dump-autoload
</code>```

Body:
Anything I try to execute in Laravel, even minor files locally gives this message in composer:

[Symfony\Component\Process\Exception\RuntimeException]

  The process has been signaled with signal "11".

Using OSX 10.7.5

Any help appreciated, as always.

For example :

composer update

typed in terminal retrieves the error message above

Body:
Try to run thin command

composer dump-autoload

ContentLicense: CC BY-SA 4.0


问:
Can you change the contents of a (immutable) string via an unsafe method?
I know that strings are immutable and any changes to a string simply creates a new string in memory (and marks the old one as free). However, I'm wondering if my logic below is sound in that you actually can, in a round-a-bout fashion, modify the contents of a string.

```<code>const string baseString = "The quick brown fox jumps over the lazy dog!";

//initialize a new string
string candidateString = new string('\0', baseString.Length);

//Pin the string
GCHandle gcHandle = GCHandle.Alloc(candidateString, GCHandleType.Pinned);

//Copy the contents of the base string to the candidate string
unsafe
{
    char* cCandidateString = (char*) gcHandle.AddrOfPinnedObject();
    for (int i = 0; i < baseString.Length; i++)
    {
        cCandidateString[i] = baseString[i];
    }
}
</code>```

Does this approach indeed change the contents candidateString (without creating a new candidateString in memory) or does the runtime see through my tricks and treat it as a normal string?

答:
As others have pointed out, mutating the String objects is useful in some rare cases. I give an example with a useful code snippet below.

Use-case/background

Although everyone should be a huge fan of the really excellent character Encoding support that .NET has always offered, sometimes it might be preferable to cut down that overhead, especially if doing a lot of roundtripping between 8-bit (legacy) characters and managed strings (i.e. typically interop scenarios).

As I hinted, .NET is particularly emphatic that you must explicitly specify a text Encoding for any/all conversions of non-Unicode character data to/from managed String objects. This rigorous control at the periphery is really commendable, since it ensures that once you have the string inside the managed runtime you never have to worry; everything is just Unicode (technically, UCS-2).

For contrast, consider a certain other popular scripting language that famously botched this whole area, resulting in an ongoing saga of parallel 2.x and 3.x versions, all due to adding support for Unicode in the latter.

By enforcing Unicode once you're inside, .NET pushes all that mess to the interop boundary where it is done "once-and-for-all", but this philosophy entails that that encoding/decoding work is eager and exhaustive, as opposed to "lazy" and more under your program's control. Because of this, the .NET Encoding/Encoder classes can be a performance bottleneck. If you're moving lots of text from wide (Unicode) to simple fixed 7- or 8-bit narrow ANSI, ASCII, etc. (note I'm not talking about MBCS or UTF-8, where you'll want to use the Encoders!), the .NET encoding paradigm might seem like overkill.

Furthermore, it could be the case that you don't know, or don't care to, specify an Encoding. Maybe all you care about is fast and accurate round-tripping for that low-byte of a 16-bit Char. If you look at the .NET source code, even the System.Text.ASCIIEncoding might be too bulky in some situations.

The code snippet...

Thin String: 8-bit characters directly stored in a managed
String, one 'thin char' per wide Unicode character, without
bothering with character encoding/decoding during round-tripping.

All of these methods just ignore/strip the upper byte of each 16-bit Unicode character, transmitting only each low byte exactly as-is. Obviously, successful recovery of the Unicode text after a round-trip will only be possible if those upper bits aren't relevant.

```<code>/// <summary> Convert byte array to "thin string" </summary>
public static unsafe String ToThinString(this byte[] src)
{
    int c;
    var ret = String.Empty;
    if ((c = src.Length) > 0)
        fixed (char* dst = (ret = new String('\0', c)))
            do
                dst[--c] = (char)src[c];  // fill new String by in-situ mutation
            while (c > 0);

    return ret;
}
</code>```

In the direction just shown, which is typically bringing native data in to managed, you often don't have the managed byte array, so rather than allocate a temporary one just for the purpose of calling this function, you can process the raw native bytes directly into a managed string. As before, this bypasses all character encoding.

The (obvious) range checks that would be needed in this unsafe function are elided for clarity:

```<code>public static unsafe String ToThinString(byte* pSrc, int c)
{
    var ret = String.Empty;
    if (c > 0)
        fixed (char* dst = (ret = new String('\0', c)))
            do
                dst[--c] = (char)pSrc[c];  // fill new String by in-situ mutation
            while (c > 0);

    return ret;
}
</code>```

The advantage of String mutation here is that you avoid temporary allocations by writing directly to the final allocation. Even if you were to avoid the extra allocation by using stackalloc, there would be an unnecessary re-copying of the whole thing when you eventually call the String(Char*, int, int) constructor: clearly there's no way to associate data you just laboriously prepared with a String object that didn't exist until you were finished!

For completeness...

Here's the mirror-code which reverses operation to get back a byte array (even though this direction doesn't happen to illustrate the string-mutation technique). This is the direction you'd typically use to send Unicode text out of the managed .NET runtime, for use by a legacy app.

```<code>/// <summary> Convert "thin string" to byte array </summary>
public static unsafe byte[] ToByteArr(this String src)
{
    int c;
    byte[] ret = null;
    if ((c = src.Length) > 0)
        fixed (byte* dst = (ret = new byte[c]))
            do
                dst[--c] = (byte)src[c];
            while (c > 0);

    return ret ?? new byte[0];
}
</code>```

Body:
I know that strings are immutable and any changes to a string simply creates a new string in memory (and marks the old one as free). However, I'm wondering if my logic below is sound in that you actually can, in a round-a-bout fashion, modify the contents of a string.

const string baseString = "The quick brown fox jumps over the lazy dog!";

//initialize a new string
string candidateString = new string('\0', baseString.Length);

//Pin the string
GCHandle gcHandle = GCHandle.Alloc(candidateString, GCHandleType.Pinned);

//Copy the contents of the base string to the candidate string
unsafe
{
    char* cCandidateString = (char*) gcHandle.AddrOfPinnedObject();
    for (int i = 0; i < baseString.Length; i++)
    {
        cCandidateString[i] = baseString[i];
    }
}

Does this approach indeed change the contents candidateString (without creating a new candidateString in memory) or does the runtime see through my tricks and treat it as a normal string?

Body:
As others have pointed out, mutating the String objects is useful in some rare cases. I give an example with a useful code snippet below.

Use-case/background

Although everyone should be a huge fan of the really excellent character Encoding support that .NET has always offered, sometimes it might be preferable to cut down that overhead, especially if doing a lot of roundtripping between 8-bit (legacy) characters and managed strings (i.e. typically interop scenarios).

As I hinted, .NET is particularly emphatic that you must explicitly specify a text Encoding for any/all conversions of non-Unicode character data to/from managed String objects. This rigorous control at the periphery is really commendable, since it ensures that once you have the string inside the managed runtime you never have to worry; everything is just Unicode (technically, UCS-2).

For contrast, consider a certain other popular scripting language that famously botched this whole area, resulting in an ongoing saga of parallel 2.x and 3.x versions, all due to adding support for Unicode in the latter.

By enforcing Unicode once you're inside, .NET pushes all that mess to the interop boundary where it is done "once-and-for-all", but this philosophy entails that that encoding/decoding work is eager and exhaustive, as opposed to "lazy" and more under your program's control. Because of this, the .NET Encoding/Encoder classes can be a performance bottleneck. If you're moving lots of text from wide (Unicode) to simple fixed 7- or 8-bit narrow ANSI, ASCII, etc. (note I'm not talking about MBCS or UTF-8, where you'll want to use the Encoders!), the .NET encoding paradigm might seem like overkill.

Furthermore, it could be the case that you don't know, or don't care to, specify an Encoding. Maybe all you care about is fast and accurate round-tripping for that low-byte of a 16-bit Char. If you look at the .NET source code, even the System.Text.ASCIIEncoding might be too bulky in some situations.

The code snippet...

Thin String: 8-bit characters directly stored in a managed
String, one 'thin char' per wide Unicode character, without
bothering with character encoding/decoding during round-tripping.

All of these methods just ignore/strip the upper byte of each 16-bit Unicode character, transmitting only each low byte exactly as-is. Obviously, successful recovery of the Unicode text after a round-trip will only be possible if those upper bits aren't relevant.

/// <summary> Convert byte array to "thin string" </summary>
public static unsafe String ToThinString(this byte[] src)
{
    int c;
    var ret = String.Empty;
    if ((c = src.Length) > 0)
        fixed (char* dst = (ret = new String('\0', c)))
            do
                dst[--c] = (char)src[c];  // fill new String by in-situ mutation
            while (c > 0);

    return ret;
}

In the direction just shown, which is typically bringing native data in to managed, you often don't have the managed byte array, so rather than allocate a temporary one just for the purpose of calling this function, you can process the raw native bytes directly into a managed string. As before, this bypasses all character encoding.

The (obvious) range checks that would be needed in this unsafe function are elided for clarity:

public static unsafe String ToThinString(byte* pSrc, int c)
{
    var ret = String.Empty;
    if (c > 0)
        fixed (char* dst = (ret = new String('\0', c)))
            do
                dst[--c] = (char)pSrc[c];  // fill new String by in-situ mutation
            while (c > 0);

    return ret;
}

The advantage of String mutation here is that you avoid temporary allocations by writing directly to the final allocation. Even if you were to avoid the extra allocation by using stackalloc, there would be an unnecessary re-copying of the whole thing when you eventually call the String(Char*, int, int) constructor: clearly there's no way to associate data you just laboriously prepared with a String object that didn't exist until you were finished!

For completeness...

Here's the mirror-code which reverses operation to get back a byte array (even though this direction doesn't happen to illustrate the string-mutation technique). This is the direction you'd typically use to send Unicode text out of the managed .NET runtime, for use by a legacy app.

/// <summary> Convert "thin string" to byte array </summary>
public static unsafe byte[] ToByteArr(this String src)
{
    int c;
    byte[] ret = null;
    if ((c = src.Length) > 0)
        fixed (byte* dst = (ret = new byte[c]))
            do
                dst[--c] = (byte)src[c];
            while (c > 0);

    return ret ?? new byte[0];
}


问:
How to find a float in a string - Python?
I want to find the first float that appears in a string using Python 3.

I looked at other similar questions but I couldn't understand them and when I tried to implement them they didn't work for my case.

An example string would be

I would like 1.5 cookies please

答:
I would use a regex. below also checks for negative values.

```<code>import re

stringToSearch = 'I would like 1.5 cookies please'
searchPattern = re.compile(".*(-?[0-9]\.[0-9]).*")
searchMatch = searchPattern.search(stringToSearch)

if searchMatch:
    floatValue = searchMatch.group(1)
else:
    raise Exception('float not found')
</code>```

You can use PyRegex to check the regex.

Body:
I would use a regex. below also checks for negative values.

import re

stringToSearch = 'I would like 1.5 cookies please'
searchPattern = re.compile(".*(-?[0-9]\.[0-9]).*")
searchMatch = searchPattern.search(stringToSearch)

if searchMatch:
    floatValue = searchMatch.group(1)
else:
    raise Exception('float not found')

You can use PyRegex to check the regex.


问:
UIView isHidden function, what does "the view's next valid key view" means?
I'm currently studying the isHidden method of UIView class.

In the Apple documentation, it mentioned

Hiding the view that is the window’s current first responder causes
  the view’s next valid key view to become the new first responder.

I was just wondering, what does the view's next valid key view means?

答:
That documentation is apparently left over from the older NSView documentation and it is incorrect.

NSView is the macOS equivalent of iOS's UIView. NSView has a nextKeyView property which indicates what view should get keyboard focus when the user tabs out of the current view. NSView also has a computed property, nextValidKeyView, which is the next view in the key view loop that accepts first responder and is not hidden.

UIView has no nextKeyView or nextValidKeyView property.

Furthermore, on iOS, hiding the current first responder does not cause “the view’s next valid key view to become the new first responder”, for any reasonable definition of “next valid key view”, because the hidden view remains first responder.

Here's a demo, recorded on a real iPhone 6 (not the simulator) running iOS 10.3.2. The “Button” toggles the isHidden property of the top text field.

After I type “hello” in the top text field, I tap the button to hide that text field. The keyboard is still active but the second text field has no blinking insertion point. I then type “world” and tap the button again. The top text field reappears, and now it says “hello world”. It continued to be first responder while it was hidden. The second text field (which is the only possible candidate for “next valid key view”) did not become first responder.


问:
Faster apply method in pandas
I have a function that I'm trying to apply to a dataframe of locations. Specifically, I want to append a new column that contains the 10 closest sites to each site. The following seems to work, but it is excruciatingly slow.

```<code>def distance(first_lat, first_lon, second_lat, second_lon):
    return ((first_lat - second_lat) ** 2 + (first_lon - second_lon) ** 2) ** 0.5

def load_site_list():
    '''
    This function generates a dataframe with all the available sites
    '''
    url = 'ftp://ftp.ncdc.noaa.gov/pub/data/noaa/isd-history.csv'
    cols = ["STATION NAME",
            "LAT",
            "LON"]
    df = pd.read_csv(url, parse_dates=False, usecols=cols)
    df = df.dropna(subset=['LAT'])
    df = df.dropna(subset=['LON'])
    df['LAT'] = df['LAT'].astype(float)
    df['LON'] = df['LON'].astype(float)
    return df

sites = load_site_list()
sites['closest'] = ""
for index, row in sites.iterrows():
    sites['dist'] = sites.apply(lambda line: distance(line['LAT'], line['LON'], row['LAT'], row['LON']), axis=1)
    sites.sort_values('dist', inplace=True)
    sites['closest'][index] = sites['STATION NAME'].iloc[1:11].tolist()
</code>```

It appears the first line in the for loop that generates the distances from the current column takes over a second per loop. There's over 10,000 rows in this to loop through...is there a faster way of doing this?

答:
Notice that your code has a time complexity of O(n^2): In this case, you're computing 30k*30k=900 million distances within an apply function that's in a for loop, i.e. pure Python.

Vector operations in pandas are implemented in C, so you would get a relative speedup if you calculated all the distances in a single vector operation.

If you had enough RAM, you could do a Cartesian join, compute all the pairwise distances, then do a sort, group by, then take the heads, like so:

```<code># code to reduce memory usage
sites['site_code'] = pd.Categorical(sites['STATION NAME']).codes
sites['LAT'] = sites.LAT.astype(np.float16)
sites['LON'] = sites.LAT.astype(np.float16)
sites_small = sites[['site_code','LAT','LON']].copy()
sites_small.index = [0]*len(sites_small)

pairs = sites_small.join(sites_small,lsuffix='_x',rsuffix='_y')
pairs['dist'] = (pairs['LAT_x'] - pairs['LAT_y'])**2 + (pairs['LON_x'] - pairs['LON_y'])**2
pairs.sort_values(['STATION NAME_x','dist'], inplace = True) # actually, just sorting by dist is sufficient
pairs.groupby('STATION NAME_x').head(10)
</code>```

Unfortunately, you probably don't have enough RAM: if you encoded the site names into 16-bit integers, and the coordinates into 16-bit floats, you need 12 bytes per row (because you're looking at pairs), plus another 8 bytes for the index (pandas brings these into longints in the join; I don't know how to get around this), which works out to about 20 bytes * 900m rows = 18GB for the final dataframe. It's probably more in practice, and peak memory usage during the operation is higher than this (in particular, the sort will take the longest, and use a ton of memory).

I tried this on my machine: I used about 30GB, gave up waiting on the full sort and sorted a subset where the dist was less than 100 instead. Took under 5 minutes, with most of the time being spent on the join.

At the end of the day, you're looking at computing close to a billion calculations; if you want to do this at the speed of C without having to store all the pairwise data (which is the case with a direct method in pandas), you most likely have to write your code in Cython using numpy arrays, and/or with multiprocessing.

The smarter approach is to avoid having to do a billion computations, which involves knowing which distances you don't need to bother computing. This needs some clever logic, but fortunately this is a well-researched topic of k-Nearest Neighbors, which has efficient algorithms specifically designed for problems of this nature:

```<code>from sklearn.neighbors import NearestNeighbors
data = sites[['LAT','LON']].values
nbrs = NearestNeighbors(n_neighbors=10, algorithm='auto', metric = 'euclidean').fit(data)
distances, indices = nbrs.kneighbors(data)
indices
</code>```

This takes under a second to compute. Recovering the names of the nearest neighbors takes a bit longer:

```<code>df = pd.DataFrame(indices, index = sites['STATION NAME'].values)
df.replace(dict(enumerate(sites['STATION NAME'].values)), inplace = True)
</code>```

(You can actually speed this up considerably by using a .merge() method with some stacking/unstacking, but in this case it's slightly trickier because your data contains duplicates.)

Title: Faster apply method in pandas

Body:
I have a function that I'm trying to apply to a dataframe of locations. Specifically, I want to append a new column that contains the 10 closest sites to each site. The following seems to work, but it is excruciatingly slow.

def distance(first_lat, first_lon, second_lat, second_lon):
    return ((first_lat - second_lat) ** 2 + (first_lon - second_lon) ** 2) ** 0.5

def load_site_list():
    '''
    This function generates a dataframe with all the available sites
    '''
    url = 'ftp://ftp.ncdc.noaa.gov/pub/data/noaa/isd-history.csv'
    cols = ["STATION NAME",
            "LAT",
            "LON"]
    df = pd.read_csv(url, parse_dates=False, usecols=cols)
    df = df.dropna(subset=['LAT'])
    df = df.dropna(subset=['LON'])
    df['LAT'] = df['LAT'].astype(float)
    df['LON'] = df['LON'].astype(float)
    return df

sites = load_site_list()
sites['closest'] = ""
for index, row in sites.iterrows():
    sites['dist'] = sites.apply(lambda line: distance(line['LAT'], line['LON'], row['LAT'], row['LON']), axis=1)
    sites.sort_values('dist', inplace=True)
    sites['closest'][index] = sites['STATION NAME'].iloc[1:11].tolist()

It appears the first line in the for loop that generates the distances from the current column takes over a second per loop. There's over 10,000 rows in this to loop through...is there a faster way of doing this?

Body:
Notice that your code has a time complexity of O(n^2): In this case, you're computing 30k*30k=900 million distances within an apply function that's in a for loop, i.e. pure Python.

Vector operations in pandas are implemented in C, so you would get a relative speedup if you calculated all the distances in a single vector operation.

If you had enough RAM, you could do a Cartesian join, compute all the pairwise distances, then do a sort, group by, then take the heads, like so:

# code to reduce memory usage
sites['site_code'] = pd.Categorical(sites['STATION NAME']).codes
sites['LAT'] = sites.LAT.astype(np.float16)
sites['LON'] = sites.LAT.astype(np.float16)
sites_small = sites[['site_code','LAT','LON']].copy()
sites_small.index = [0]*len(sites_small)

pairs = sites_small.join(sites_small,lsuffix='_x',rsuffix='_y')
pairs['dist'] = (pairs['LAT_x'] - pairs['LAT_y'])**2 + (pairs['LON_x'] - pairs['LON_y'])**2
pairs.sort_values(['STATION NAME_x','dist'], inplace = True) # actually, just sorting by dist is sufficient
pairs.groupby('STATION NAME_x').head(10)

Unfortunately, you probably don't have enough RAM: if you encoded the site names into 16-bit integers, and the coordinates into 16-bit floats, you need 12 bytes per row (because you're looking at pairs), plus another 8 bytes for the index (pandas brings these into longints in the join; I don't know how to get around this), which works out to about 20 bytes * 900m rows = 18GB for the final dataframe. It's probably more in practice, and peak memory usage during the operation is higher than this (in particular, the sort will take the longest, and use a ton of memory).

I tried this on my machine: I used about 30GB, gave up waiting on the full sort and sorted a subset where the dist was less than 100 instead. Took under 5 minutes, with most of the time being spent on the join.

At the end of the day, you're looking at computing close to a billion calculations; if you want to do this at the speed of C without having to store all the pairwise data (which is the case with a direct method in pandas), you most likely have to write your code in Cython using numpy arrays, and/or with multiprocessing.

The smarter approach is to avoid having to do a billion computations, which involves knowing which distances you don't need to bother computing. This needs some clever logic, but fortunately this is a well-researched topic of k-Nearest Neighbors, which has efficient algorithms specifically designed for problems of this nature:

from sklearn.neighbors import NearestNeighbors
data = sites[['LAT','LON']].values
nbrs = NearestNeighbors(n_neighbors=10, algorithm='auto', metric = 'euclidean').fit(data)
distances, indices = nbrs.kneighbors(data)
indices

This takes under a second to compute. Recovering the names of the nearest neighbors takes a bit longer:

df = pd.DataFrame(indices, index = sites['STATION NAME'].values)
df.replace(dict(enumerate(sites['STATION NAME'].values)), inplace = True)

(You can actually speed this up considerably by using a .merge() method with some stacking/unstacking, but in this case it's slightly trickier because your data contains duplicates.)


问:
BufferedImage in java larger than 2^31 pixels
I need to create a very large image in java and so far I've been using the java.awt.image.BufferedImage class. But it seems that this class stores it's pixels with an int as it can not go past 2^31 pixels. I've thought about creating smaller images and stitching them together, but the problem is the photo editors I try like Adobe Photoshop can't even  import photos larger than 30,000 pixels in any dimension.

I've thought about making my own image class but I am not knowledgeable enough to make everything to go into that.

Any suggestions?
Thank You

答: I found out that GIMP is able to import photos that are 2^31 or less in any dimension so I'm able to stitch together photos with this. Thank you to everyone that responded. </p>


问:
Elegant way to combine multiple collections of elements?
Say I have an arbitrary number of collections, each containing objects of the same type (for example, List<int> foo and List<int> bar). If these collections were themselves in a collection (e.g., of type List<List<int>>, I could use SelectMany to combine them all into one collection.

However, if these collections are not already in the same collection, it's my impression that I'd have to write a method like this:

```<code>public static IEnumerable<T> Combine<T>(params ICollection<T>[] toCombine)
{
   return toCombine.SelectMany(x => x);
}
</code>```

Which I'd then call like this:

```<code>var combined = Combine(foo, bar);
</code>```

Is there a clean, elegant way to combine (any number of) collections without having to write a utility method like Combine above? It seems simple enough that there should be a way to do it in LINQ, but perhaps not.

答:
The only way I see is to use Concat()

```<code> var foo = new List<int> { 1, 2, 3 };
 var bar = new List<int> { 4, 5, 6 };
 var tor = new List<int> { 7, 8, 9 };

 var result = foo.Concat(bar).Concat(tor);
</code>```

But you should decide what is better:

```<code>var result = Combine(foo, bar, tor);
</code>```

or

```<code>var result = foo.Concat(bar).Concat(tor);
</code>```

One point why Concat() will be a better choice is that it will be more obvious for another developer. More readable and simple.

ContentLicense: CC BY-SA 2.5

Body:
Say I have an arbitrary number of collections, each containing objects of the same type (for example, List<int> foo and List<int> bar). If these collections were themselves in a collection (e.g., of type List<List<int>>, I could use SelectMany to combine them all into one collection.

However, if these collections are not already in the same collection, it's my impression that I'd have to write a method like this:

public static IEnumerable<T> Combine<T>(params ICollection<T>[] toCombine)
{
   return toCombine.SelectMany(x => x);
}

Which I'd then call like this:

var combined = Combine(foo, bar);

Is there a clean, elegant way to combine (any number of) collections without having to write a utility method like Combine above? It seems simple enough that there should be a way to do it in LINQ, but perhaps not.

Body:
The only way I see is to use Concat()

 var foo = new List<int> { 1, 2, 3 };
 var bar = new List<int> { 4, 5, 6 };
 var tor = new List<int> { 7, 8, 9 };

 var result = foo.Concat(bar).Concat(tor);

But you should decide what is better:

var result = Combine(foo, bar, tor);

or

var result = foo.Concat(bar).Concat(tor);

One point why Concat() will be a better choice is that it will be more obvious for another developer. More readable and simple.


问:
Firebase add another data to a child without overwriting existing data
I have this following method which i use when saving a new data

```<code>public void registerNewUser(FirebaseUser id,
                       String name,
                       String email,
                       int count) {

    User user = new User(name, email, count);

    DatabaseReference ref = FirebaseDatabase.getInstance()
            .getReference();
    ref.child("path")
            .child(id.getUid()).setValue(user);
}
</code>```

I like to add a new data in the same child/folder when the user is done registering, this is what i have done so far.

```<code>    DatabaseReference ref = FirebaseDatabase.getInstance()
            .getReference();
    ref.child("path")
            .child(id.getUid()).setValue("THE NEW DATA");
</code>```

But the problem is it clears and overwrite the old data. Is there any efficient way to do this?

答:
You can try this code

```<code>DatabaseReference ref = FirebaseDatabase.getInstance()
        .getReference();
ref.child("path")
        .child(id.getUid()+"/username").setValue(user);
ref.child("path")
        .child(id.getUid()+"/newdata").setValue("THE NEW DATA");
</code>```

Below Image Output Code

```<code>ref.child("path")
    .child(id.getUid()+"/username").setValue(["username": "isabellap"]);
ref.child("path")
    .child(id.getUid()+"/build").setValue(["build": "1.0.7"]);
</code>```

Output

Body:
I have this following method which i use when saving a new data

public void registerNewUser(FirebaseUser id,
                       String name,
                       String email,
                       int count) {

    User user = new User(name, email, count);

    DatabaseReference ref = FirebaseDatabase.getInstance()
            .getReference();
    ref.child("path")
            .child(id.getUid()).setValue(user);
}

I like to add a new data in the same child/folder when the user is done registering, this is what i have done so far.

    DatabaseReference ref = FirebaseDatabase.getInstance()
            .getReference();
    ref.child("path")
            .child(id.getUid()).setValue("THE NEW DATA");

But the problem is it clears and overwrite the old data. Is there any efficient way to do this?

Body:
You can try this code

DatabaseReference ref = FirebaseDatabase.getInstance()
        .getReference();
ref.child("path")
        .child(id.getUid()+"/username").setValue(user);
ref.child("path")
        .child(id.getUid()+"/newdata").setValue("THE NEW DATA");

Below Image Output Code

ref.child("path")
    .child(id.getUid()+"/username").setValue(["username": "isabellap"]);
ref.child("path")
    .child(id.getUid()+"/build").setValue(["build": "1.0.7"]);

Output


问:
How to pull new objects from Django view to implement endless scrolling?
I have a Django app for news articles. I want to first load 6 articles and when user scrolls through them, new 6 articles get automatically loaded (twitter-style endless scrolling):

I found this package but it's deprecated so I'm doing it as below.

This is the view:

```<code>def index(request):
    context_dict = {}

    articles = Article.objects.order_by('-pub_date')[:6]
    context_dict['articles'] =  articles
    context_dict['title'] = "title "
    context_dict['endless_scroll_token'] = '/home/'

    return render(request, 'obj_name/index.html', context_dict)
</code>```

This is the java-script / AJAX code:

```<code>  <script>
  function yHandler(){

      var wrap = document.getElementById('body_panel');
      var contentHeight = wrap.offsetHeight;
      var yOffset = window.pageYOffset;
      var y = yOffset + window.innerHeight;

      if(y >= contentHeight){
        var ourRequest = new XMLHttpRequest();
        var token = {{endless_scroll_token}};

        if(token == "/home/")
          {token = '/';}

        ourRequest.open('GET', token);
        ourRequest.send();

        ourRequest.onload = function(){
        var ourData = ourRequest.responseText;
        wrap.innerHTML += ourData

      };
    }
  }
  window.onscroll = yHandler;
</script>
</code>```

The problem is that this way, the same 6 articles are loaded over and over. How do I pull 6 new articles each time ?

Thanks!

答:
It's called Pagination

There are many ways to page data.
You can read http://www.django-rest-framework.org/api-guide/pagination/

For instance, you tell server that you want #2 page of the data.
and you and server also need to know how many items are included in each page.  (This is PageNumberPagination in the above doc)

I strongly suggest you to look at Django rest framework because it gives you many functionality including pagination.

Body:
I have a Django app for news articles. I want to first load 6 articles and when user scrolls through them, new 6 articles get automatically loaded (twitter-style endless scrolling):

I found this package but it's deprecated so I'm doing it as below.

This is the view:

def index(request):
    context_dict = {}

    articles = Article.objects.order_by('-pub_date')[:6]
    context_dict['articles'] =  articles
    context_dict['title'] = "title "
    context_dict['endless_scroll_token'] = '/home/'

    return render(request, 'obj_name/index.html', context_dict)

This is the java-script / AJAX code:

The problem is that this way, the same 6 articles are loaded over and over. How do I pull 6 new articles each time ?

Thanks!


问:
Image to Tkinter Window
I am trying to place a image into my tkinter window and getting error not sure what is causing it. The path is 100% to the location on my computer.

```<code>import tkinter

import os

from PIL import ImageTk, Image

#Actual window
window = tkinter.Tk()
window.geometry('400x400')
window.title('Game')
window.configure(bg = 'Indian red')

#Pictures
path = 'E:\Hack Things\python\amazonlogo'
img = ImageTk.PhotoImage(Image.open(path))
panel = tk.Label(window, image = img)
panel.pack(side = "bottom", fill = "both", expand = "yes")

#Buttons
start = tkinter.Button(text = 'Start', height = 5, width = 25, font =
('Helvetica', 12))

start.place(relx = .5, rely = .25, anchor='c')

options = tkinter.Button(text="Options")
options.place(relx = .5, rely = .45, anchor='c')

exitbutton = tkinter.Button(text = 'Exit Game', command = quit, height = 5,
width = 15, fg = 'red')
exitbutton.place(relx = .5, rely = .85, anchor='c')

window.mainloop()
</code>```

I am getting this error

```<code>Traceback (most recent call last):
  File "E:\Hack Things\python\start.py", line 16, in <module>
    img = ImageTk.PhotoImage(Image.open(path))
  File "C:\Users\Ihas\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\PIL\Image.py", line 2477, in open
    fp = builtins.open(filename, "rb")
OSError: [Errno 22] Invalid argument: 'E:\\Hack Things\\python\x07mazonlogo'
</code>```

答:
You will need to use raw string (by adding a r in front the quotes). Or escape the back slashes (by adding a \ in front every \). Since the \a has a special meaning which is “ASCII Bell (BEL)”

```<code>path = r'E:\Hack Things\python\amazonlogo'
</code>```

Title: Image to Tkinter Window

Body:
I am trying to place a image into my tkinter window and getting error not sure what is causing it. The path is 100% to the location on my computer.

import tkinter

import os

from PIL import ImageTk, Image

#Actual window
window = tkinter.Tk()
window.geometry('400x400')
window.title('Game')
window.configure(bg = 'Indian red')

#Pictures
path = 'E:\Hack Things\python\amazonlogo'
img = ImageTk.PhotoImage(Image.open(path))
panel = tk.Label(window, image = img)
panel.pack(side = "bottom", fill = "both", expand = "yes")

#Buttons
start = tkinter.Button(text = 'Start', height = 5, width = 25, font =
('Helvetica', 12))

start.place(relx = .5, rely = .25, anchor='c')

options = tkinter.Button(text="Options")
options.place(relx = .5, rely = .45, anchor='c')

exitbutton = tkinter.Button(text = 'Exit Game', command = quit, height = 5,
width = 15, fg = 'red')
exitbutton.place(relx = .5, rely = .85, anchor='c')

window.mainloop()

I am getting this error

Traceback (most recent call last):
  File "E:\Hack Things\python\start.py", line 16, in <module>
    img = ImageTk.PhotoImage(Image.open(path))
  File "C:\Users\Ihas\AppData\Local\Programs\Python\Python36-32\lib\site-
packages\PIL\Image.py", line 2477, in open
    fp = builtins.open(filename, "rb")
OSError: [Errno 22] Invalid argument: 'E:\\Hack Things\\python\x07mazonlogo'

Body:
You will need to use raw string (by adding a r in front the quotes). Or escape the back slashes (by adding a \ in front every \). Since the \a has a special meaning which is “ASCII Bell (BEL)”

path = r'E:\Hack Things\python\amazonlogo'


问:
Get the content of siblings elements with jQuery
I need to get the content of some specific elements using jquery, the problem is that i have the same element repeating on my page:

```<code><div class="content">
   <img alt="" src="">
   <p class="txt">Lorem ipsum dolor sit amet, consectetur adipisicing elit.</p>
   <p class="info">Lorem ipsum dolor...</p>
   <ul>
     <li><a class="uiButton">Test</a></li>
   </ul>
 </div>
</code>```

So, the div.content repeat on my page many times and I want to get only the contents of the div that holds the button was clicked. I've added a onclick event on the .uiButton and tried to use parent(".txt") or siblings(".txt"), but dont seems to work...

```<code>$(".uiButton").click(function(){
  var txt = $(this).parent(".txt").text();
}
</code>```

答:
```<code>$(this).closest('p.txt');
</code>```

Might work.

Body:
I need to get the content of some specific elements using jquery, the problem is that i have the same element repeating on my page:

Lorem ipsum dolor sit amet, consectetur adipisicing elit.

Lorem ipsum dolor...

Test

So, the div.content repeat on my page many times and I want to get only the contents of the div that holds the button was clicked. I've added a onclick event on the .uiButton and tried to use parent(".txt") or siblings(".txt"), but dont seems to work...

$(".uiButton").click(function(){
  var txt = $(this).parent(".txt").text();
}

Body:
$(this).closest('p.txt');

Might work.


问:
Drupal only display certain content based on location
I need to be able to only display certain content/nodes based on where a user is located in the world. For example, if the user is from the US, they should see different results than someone from Europe.

Ideally I would be able to tag each node with the region(s) that it should be displayed in and then when the content is displayed, it is filtered by these tags.

I'm also going to be using various content 'scrollers' to display content and I would like these to have different content based on location as well.

How would I best achieve this?

答:
I know this is an old thread, but it's one that doesn't necessarily  have an easy solution in Drupal. Here's how I did it. This solution doesn't match the original question exactly, but this explains a method that could be extended to cover that scenario by extending the PHP code to compare the user's location to the taxonomy term(s) assigned to each node.

First, install IP2Country. This allows you to match an IP address to someone's country by doing a geolocation lookup.

Second, use PHP code like this to determine if the visitor's IP address matches a certain country. This example returns true if the visitor is in the US or Canada, and false if they are not.

```<code><?php
    $detectedcountry = module_invoke('ip2country','get_country',$_SERVER['REMOTE_ADDR']);
    $countries = array(
        'US','CA'
    );
    if(in_array($detectedcountry,$countries)){
        return true;
    }
    else return false;
?>
</code>```

You can use this code in various places, but you will need to enable the PHP filter module for it to work.

In my solution, I used this code in two ways:

First, I created a Panels Variant for a Node Template (could be a Taxonomy Term template as well) to return a HTTP response rather than a Panel Pane. I used this code as a selection rule, so it would return a 404 if the user was not in a country that was allowed to see that specific node type. I then created Panels variant that returned the node page for anyone who qualified based on their location.

Second, I created a Panels variant for a node type (Product, in this case) and used the same PHP code to hide certain elements on the page for those users who weren't allowed to see them because of their location. In this case, I hid the Price and Add to Cart panes on a product display page. Visitors outside the allowed countries only saw the product information, and not the ecommerce elements on the page.

As a third option, you could also use it to show/hide Drupal blocks using the same code.

Finally, I created a custom theme function so I could show/hide individual menu items depending on a person's location. In this case, I wanted to hide the Shop menu item unless you were in the permitted countries. I wrote about how to do that in more detail here: https://fiveminutelessons.com/learn-drupal-sitebuilding/show-or-hide-menu-item-drupal-7-based-users-location

Body:
I know this is an old thread, but it's one that doesn't necessarily  have an easy solution in Drupal. Here's how I did it. This solution doesn't match the original question exactly, but this explains a method that could be extended to cover that scenario by extending the PHP code to compare the user's location to the taxonomy term(s) assigned to each node.

First, install IP2Country. This allows you to match an IP address to someone's country by doing a geolocation lookup.

Second, use PHP code like this to determine if the visitor's IP address matches a certain country. This example returns true if the visitor is in the US or Canada, and false if they are not.

<?php
    $detectedcountry = module_invoke('ip2country','get_country',$_SERVER['REMOTE_ADDR']);
    $countries = array(
        'US','CA'
    );
    if(in_array($detectedcountry,$countries)){
        return true;
    }
    else return false;
?>

You can use this code in various places, but you will need to enable the PHP filter module for it to work.

In my solution, I used this code in two ways:

First, I created a Panels Variant for a Node Template (could be a Taxonomy Term template as well) to return a HTTP response rather than a Panel Pane. I used this code as a selection rule, so it would return a 404 if the user was not in a country that was allowed to see that specific node type. I then created Panels variant that returned the node page for anyone who qualified based on their location.

Second, I created a Panels variant for a node type (Product, in this case) and used the same PHP code to hide certain elements on the page for those users who weren't allowed to see them because of their location. In this case, I hid the Price and Add to Cart panes on a product display page. Visitors outside the allowed countries only saw the product information, and not the ecommerce elements on the page.

As a third option, you could also use it to show/hide Drupal blocks using the same code.

Finally, I created a custom theme function so I could show/hide individual menu items depending on a person's location. In this case, I wanted to hide the Shop menu item unless you were in the permitted countries. I wrote about how to do that in more detail here: https://fiveminutelessons.com/learn-drupal-sitebuilding/show-or-hide-menu-item-drupal-7-based-users-location


答:
Use Enumerable.Concat like so:

```<code>var combined = foo.Concat(bar).Concat(baz)....;
</code>```

Title: Elegant way to combine multiple collections of elements?

Body:
Use Enumerable.Concat like so:

var combined = foo.Concat(bar).Concat(baz)....;


问:
NULL or BLANK fields (ORACLE)
I'm new to Oracle, so my question might sound silly. I did go through the previous posts, but no luck.
In the table, there is a column which is blank, and i am trying to find out the blank count in the column.
I tried:

```<code>SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL
SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME = ' '
SELECT COUNT (COL_NAME) FROM TABLE WHERE TRIM (COL_NAME)= ' '
</code>```

The result to all the queries above is 0

However, when i did

```<code>SELECT COL_NAME DUMP (COL_NAME,1016) FROM TABLE
</code>```

gave me:

```<code>COL_NAME       DUMP (COL_NAME,1016)
               NULL
               NULL
               NULL
</code>```

and so on..

But there are hundreds or thousands of blank fields/empty fields in that column. Can anyone please help me to find count of those blank/empty fields in that column ?
I am using Toad for Oracle 9.0.1.8

答:
One should NEVER treat "BLANK" and NULL as the same.

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/ VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL). By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2 in Oracle since that data type's behavior is guaranteed to remain the same going forward).

Title: NULL or BLANK fields (ORACLE)

Body:
I'm new to Oracle, so my question might sound silly. I did go through the previous posts, but no luck.
In the table, there is a column which is blank, and i am trying to find out the blank count in the column.
I tried:

SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME IS NULL
SELECT COUNT (COL_NAME) FROM TABLE WHERE COL_NAME = ' '
SELECT COUNT (COL_NAME) FROM TABLE WHERE TRIM (COL_NAME)= ' '

The result to all the queries above is 0

However, when i did

SELECT COL_NAME DUMP (COL_NAME,1016) FROM TABLE

gave me:

COL_NAME       DUMP (COL_NAME,1016)
               NULL
               NULL
               NULL

and so on..

But there are hundreds or thousands of blank fields/empty fields in that column. Can anyone please help me to find count of those blank/empty fields in that column ?
I am using Toad for Oracle 9.0.1.8


问:
How can I deal with my released app's crashes in main.m?
I used Bugly(a tool same as Crashlytics) to recored crashes in my app which had released.But sometimes there are some crashes in main.m as the report showed:

With these information I can't location problem in my code at all.I need to know which class and which method the problem occured so I can location this problem.

I wonder is there any way I can location problem more precise?And how do you deal with these crashes in your released app?

Wish you can share,thanks a lot.

答: Finally I find this is memory leak result.Use Xcode instrument check is a good way.</p>


问:
About resolving Resource Leaks
I'm a newbee to spring framework and I encounted some errors when I was compiling the "HelloSpring" code on tutorialscom. Throughout the code there is only one warning in the main class:

```<code>package com.tutorialspoint;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainApp {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
        HelloWorld  HW = (HelloWorld)context.getBean("Hello Spring");
        HW.getMsg();
    }
}
</code>```

Eclipse warns me "Resource Leak: 'context' is never close", but for ApplicationContext class I dont know how to close it. Can anyone help me with it?

The errors are:

```<code>Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/logging/LogFactory
    at org.springframework.context.support.AbstractApplicationContext.<init>(AbstractApplicationContext.java:164)
    at org.springframework.context.support.AbstractRefreshableApplicationContext.<init>(AbstractRefreshableApplicationContext.java:89)
    at org.springframework.context.support.AbstractRefreshableConfigApplicationContext.<init>(AbstractRefreshableConfigApplicationContext.java:58)
    at org.springframework.context.support.AbstractXmlApplicationContext.<init>(AbstractXmlApplicationContext.java:61)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:136)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
    at com.tutorialspoint.MainApp.main(MainApp.java:10)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    ... 7 more
</code>```

BTW my java version is the latest 1.80, idk if this matters.
Thank you!

答:
Add commons-logging.jar to the project classpath. This will fix the error.

Regarding the warning by eclipse, you can place the code within try block and close the connection in finally block,it will fix the warning

```<code>ApplicationContext context = null;
try{
    context = new ClassPathXmlApplicationContext("Beans.xml");
      }
finally{
  ((ClassPathXmlApplicationContext) context).close();

       }
</code>```

Title: About resolving Resource Leaks

Body:
I'm a newbee to spring framework and I encounted some errors when I was compiling the "HelloSpring" code on tutorialscom. Throughout the code there is only one warning in the main class:

package com.tutorialspoint;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MainApp {
    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
        HelloWorld  HW = (HelloWorld)context.getBean("Hello Spring");
        HW.getMsg();
    }
}

Eclipse warns me "Resource Leak: 'context' is never close", but for ApplicationContext class I dont know how to close it. Can anyone help me with it?

The errors are:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/logging/LogFactory
    at org.springframework.context.support.AbstractApplicationContext.<init>(AbstractApplicationContext.java:164)
    at org.springframework.context.support.AbstractRefreshableApplicationContext.<init>(AbstractRefreshableApplicationContext.java:89)
    at org.springframework.context.support.AbstractRefreshableConfigApplicationContext.<init>(AbstractRefreshableConfigApplicationContext.java:58)
    at org.springframework.context.support.AbstractXmlApplicationContext.<init>(AbstractXmlApplicationContext.java:61)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:136)
    at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:83)
    at com.tutorialspoint.MainApp.main(MainApp.java:10)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    ... 7 more

BTW my java version is the latest 1.80, idk if this matters.
Thank you!

Body:
Add commons-logging.jar to the project classpath. This will fix the error.

Regarding the warning by eclipse, you can place the code within try block and close the connection in finally block,it will fix the warning

ApplicationContext context = null;
try{
    context = new ClassPathXmlApplicationContext("Beans.xml");
      }
finally{
  ((ClassPathXmlApplicationContext) context).close();

       }