问:
'No such file or directory' when connecting to MySQL Server in Google App Engine in PHP
I am trying to connect to my Google Cloud SQL instance with PHP (I am using PDO). When running my PHP file (from in the Google Cloud terminal), I receive the following error:

```<code>Uncaught PDOException: SQLSTATE[HY000] [2002] No such file or directory in /home/bob_brown_the_dev/index.php:17
Stack trace:
#0 /home/bob_brown_the_dev/index.php(17): PDO->__construct('mysql:dbname=ma...', 'root', '', NULL)
#1 {main}
  thrown in /home/bob_brown_the_dev/index.php on line 17
</code>```

This is what my PHP file looks like:

<?php

$username = 'root';
$password = getenv("MYSQL_PASS");
$dbName = 'main';
$connectionName = getenv("INSTANCE_CONNECTION_NAME");
$socketDir = getenv('DB_SOCKET_DIR') ?: '/cloudsql';

$dsn = sprintf(
    'mysql:dbname=%s;unix_socket=%s/%s',
    $dbName,
    $socketDir,
    $connectionName
);

// Connect to the database.
$conn = new PDO($dsn, $username, $password, $conn_config);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";

?>
```
<p>And this is what my app.yaml file looks like:</p>
<pre class="lang-yaml prettyprint-override"><code>runtime: php73

entrypoint:
  serve index.php

env_variables:
  APP_DEBUG: true
  LOG_CHANNEL: stderr
  APP_STORAGE: /tmp
  INSTANCE_CONNECTION_NAME: "<instance-id>:us-west2:<name>"
  DB_SOCKET_DIR: "/cloudsql/<instance-id>:us-west2:<name>"
  MYSQL_PASS: <password>
  DB_CONNECTION: mysql

beta_settings:
    cloud_sql_instances: "<instance-id>:us-west2:<name>"
</code>```

Thanks in advance for any help!

EDIT:

It appears there was a problem when I fetched the environmental variables. Once I fixed that problem, the error changed to:

```<code>Uncaught PDOException: SQLSTATE[HY000] [2002] Not a directory in /home/bob_brown_the_dev/index.php:17
Stack trace:
#0 /home/bob_brown_the_dev/index.php(17): PDO->__construct('mysql:host=loca...', '...', '...')
#1 {main}
  thrown in /home/bob_brown_the_dev/index.php on line 17
</code>```

This is what the DSN looks like when I return it:

```<code>mysql:dbname=main;unix_socket=/cloudsql/<name>:us-west2:<other name>/<name>:us-west2:<other name>
</code>```

答:
If you're running your app in Cloud Shell, then you'll need to start the Cloud SQL Auth Proxy to mimic what App Engine does for you.

Start the proxy like this:

```<code>$ cloud_sql_proxy -instances=<project-id>:us-west2:<instance-name> -dir /cloudsql
</code>```

Note if /cloudsql doesn't exist, you'll have to manually create it.

Body:
I am trying to connect to my Google Cloud SQL instance with PHP (I am using PDO). When running my PHP file (from in the Google Cloud terminal), I receive the following error:

Uncaught PDOException: SQLSTATE[HY000] [2002] No such file or directory in /home/bob_brown_the_dev/index.php:17
Stack trace:
#0 /home/bob_brown_the_dev/index.php(17): PDO->__construct('mysql:dbname=ma...', 'root', '', NULL)
#1 {main}
  thrown in /home/bob_brown_the_dev/index.php on line 17

This is what my PHP file looks like:

<?php

$username = 'root';
$password = getenv("MYSQL_PASS");
$dbName = 'main';
$connectionName = getenv("INSTANCE_CONNECTION_NAME");
$socketDir = getenv('DB_SOCKET_DIR') ?: '/cloudsql';

$dsn = sprintf(
    'mysql:dbname=%s;unix_socket=%s/%s',
    $dbName,
    $socketDir,
    $connectionName
);

// Connect to the database.
$conn = new PDO($dsn, $username, $password, $conn_config);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";

?>

And this is what my app.yaml file looks like:

runtime: php73

entrypoint:
  serve index.php

env_variables:
  APP_DEBUG: true
  LOG_CHANNEL: stderr
  APP_STORAGE: /tmp
  INSTANCE_CONNECTION_NAME: "<instance-id>:us-west2:<name>"
  DB_SOCKET_DIR: "/cloudsql/<instance-id>:us-west2:<name>"
  MYSQL_PASS: <password>
  DB_CONNECTION: mysql

beta_settings:
    cloud_sql_instances: "<instance-id>:us-west2:<name>"

Thanks in advance for any help!

EDIT:

It appears there was a problem when I fetched the environmental variables. Once I fixed that problem, the error changed to:

Uncaught PDOException: SQLSTATE[HY000] [2002] Not a directory in /home/bob_brown_the_dev/index.php:17
Stack trace:
#0 /home/bob_brown_the_dev/index.php(17): PDO->__construct('mysql:host=loca...', '...', '...')
#1 {main}
  thrown in /home/bob_brown_the_dev/index.php on line 17

This is what the DSN looks like when I return it:

mysql:dbname=main;unix_socket=/cloudsql/<name>:us-west2:<other name>/<name>:us-west2:<other name>

Body:
If you're running your app in Cloud Shell, then you'll need to start the Cloud SQL Auth Proxy to mimic what App Engine does for you.

Start the proxy like this:

$ cloud_sql_proxy -instances=<project-id>:us-west2:<instance-name> -dir /cloudsql

Note if /cloudsql doesn't exist, you'll have to manually create it.


来源: stackexchange

问:
Is it possible to dynamically reference a list from a file?
I'm currently working on a python bot that references variables from different users based on the input from the user. The user enters in the name of a file in the folder, which has a list saved to it. All of the files have the same list name, but each list has different elements saved. The idea is that the files act as accounts for the user, and each one has its own keys saved in the list.

I want to then set a variable to the contents of the list from whatever file the user selected, like so:

```<code>import file1
import file2
import file3

filePath = input()
fileContents = filePath.list
</code>```

This displays an error because the filePath variable is technically a string and does not have a list attribute.

My question is:

How can I set fileContents to the contents of the list that the user chosen file contains?

答:
You want to select one of three objects based on user input?

import file1
import file2
import file3

file_lists = {
    "file1": file1.list,
    "file2": file2.list,
    "file3": file3.list,
}

while True:
    selection = input("Select file containing list: ").lower()
    if selection in file_lists:
        break
    print(f"Selection must be one of: {', '.join(file_lists)}")

fileContents = file_lists[selection]
```

ContentLicense: CC BY-SA 4.0

Body:
I'm currently working on a python bot that references variables from different users based on the input from the user. The user enters in the name of a file in the folder, which has a list saved to it. All of the files have the same list name, but each list has different elements saved. The idea is that the files act as accounts for the user, and each one has its own keys saved in the list.

I want to then set a variable to the contents of the list from whatever file the user selected, like so:

import file1
import file2
import file3

filePath = input()
fileContents = filePath.list

This displays an error because the filePath variable is technically a string and does not have a list attribute.

My question is:

How can I set fileContents to the contents of the list that the user chosen file contains?


问:
What is consuming over 65% of time in an ASP.NET application?
I have an .Net Framework 4.0, ASP.NET, ASP.NET MVC 3 web application hosted on a Windows 7 / IIS 7.5. IIS logging is enabled on this machine and set to log in W3C mode.

The application is compiled by using the Release configuration and has been deployed to IIS with <compilation debug='false' attribute set explicitly. The Web.config specifies the use of SQL Server based session state.

I have added the following statements in Global.asax in BeginRequest and EndRequest events respectively. The results i.e. "sw.Elapsed.TotalMilliseconds" are getting stored in an Application level list of values. I dump these values out via a debug page and get an average of the same.

```<code>// in BeginRequest
HttpContext.Current.Items.Add("RequestStartEnd", System.Diagnostics.Stopwatch.StartNew());

// in EndRequest
var sw = (System.Diagnostics.Stopwatch)HttpContext.Current.Items["RequestStartEnd"];
sw.Stop();
</code>```

I have created a load test which runs a single request against this application with a concurrent user load of 20 users. The test is run in Visual Studio 2010 Ultimate edition.

After running the load test, I am getting an average time-taken as recorded by the stopwatch as 681 milliseconds.The average time-taken as per IIS for these requests (I cleaned out all logs before running the load test) is 2121 milliseconds. The average time-taken as per IIS tallys with the value shown in Visual Studio load test report.

The stopwatch time-taken only accounts for 32% of time-taken as reported by IIS logs / Visual Studio. Where does the other 68% time go?

Update 1:
I set the session state to InProc and re-ran the load test. In this scenario the difference between the average time reported by stopwatch and average time-taken reported by IIS logs grew to more than 70%!!! Where is all that time going?

Update 2:
@Peter - I tried out the failed request tracing by putting a trace rule to log on status code of 200. Next, I ran the load test with 20 concurrent users for approx 1.5 minutes. Went through last 50 trace files and found that the "Time Taken" field in that report had range of 750ms to 1300ms. The Visual Studio report showed avg. time taken as 2300ms. In the report, using the compact view, I see that the time taken changes between the following transitions
    (1) AspNetStart -> AspNetAppDomainEnter
    (2) ManagedPipelineHandler-start ManagedPipelineHandler-end.
The (2) item is probably my application's code. Still there is a big difference between max of time-taken as per failed request logs i.e. 1300ms and the avg. time-taken as shown by Visual Studio 2300ms. How to find accounting for that? Thanks for this great tip though!

答:
if you are having issues with long load times it may be related to several things if you have a  high number of IO operations it can affect this
if you are doing database queries with an orm try profiling your sql statements either in vs with a sql profiler or in sql servers built in profiler if you are getting alot of individual querys you may want to consider using some includes in you linq queries to bundle up some of your data into single queries

you may also want to consider want to consider using async controllers to improve response times if you have alot of IO operations so your application isnt continuously waiting for each io operation to complete
see  wintellect.com/CS/blogs/jprosise/archive/2010/03/29/…

there also better solutions out there for logging than the iis logging as it is a fairly old component you may want to take a look at log4net for more general logging or elmah for logging errors and exceptions as these solutions can bundle up a bunch of log entries and write several in a single operation also improving IO performance

ContentLicense: CC BY-SA 3.0

Body:
I have an .Net Framework 4.0, ASP.NET, ASP.NET MVC 3 web application hosted on a Windows 7 / IIS 7.5. IIS logging is enabled on this machine and set to log in W3C mode.

The application is compiled by using the Release configuration and has been deployed to IIS with <compilation debug='false' attribute set explicitly. The Web.config specifies the use of SQL Server based session state.

I have added the following statements in Global.asax in BeginRequest and EndRequest events respectively. The results i.e. "sw.Elapsed.TotalMilliseconds" are getting stored in an Application level list of values. I dump these values out via a debug page and get an average of the same.

// in BeginRequest
HttpContext.Current.Items.Add("RequestStartEnd", System.Diagnostics.Stopwatch.StartNew());

// in EndRequest
var sw = (System.Diagnostics.Stopwatch)HttpContext.Current.Items["RequestStartEnd"];
sw.Stop();

I have created a load test which runs a single request against this application with a concurrent user load of 20 users. The test is run in Visual Studio 2010 Ultimate edition.

After running the load test, I am getting an average time-taken as recorded by the stopwatch as 681 milliseconds.The average time-taken as per IIS for these requests (I cleaned out all logs before running the load test) is 2121 milliseconds. The average time-taken as per IIS tallys with the value shown in Visual Studio load test report.

The stopwatch time-taken only accounts for 32% of time-taken as reported by IIS logs / Visual Studio. Where does the other 68% time go?

Update 1:
I set the session state to InProc and re-ran the load test. In this scenario the difference between the average time reported by stopwatch and average time-taken reported by IIS logs grew to more than 70%!!! Where is all that time going?

Update 2:
@Peter - I tried out the failed request tracing by putting a trace rule to log on status code of 200. Next, I ran the load test with 20 concurrent users for approx 1.5 minutes. Went through last 50 trace files and found that the "Time Taken" field in that report had range of 750ms to 1300ms. The Visual Studio report showed avg. time taken as 2300ms. In the report, using the compact view, I see that the time taken changes between the following transitions
    (1) AspNetStart -> AspNetAppDomainEnter
    (2) ManagedPipelineHandler-start ManagedPipelineHandler-end.
The (2) item is probably my application's code. Still there is a big difference between max of time-taken as per failed request logs i.e. 1300ms and the avg. time-taken as shown by Visual Studio 2300ms. How to find accounting for that? Thanks for this great tip though!


问:
RedirecToAction doesnt call the expected view
I have successfully created a product but the page doesn't redirects to the view I want...and I don't really know why...
[HttpPost]
public async Task AddProduct([FromForm] AddProductViewModel model)

The jquery call:

 $.ajax({
        url: '/' + 'Product' + '/' + 'AddProduct',
        type: 'POST',
        contentType: false,
        processData: false,
        data: formData,
        success: console.log('success'),

    });
//edit

   $.ajax({
        url: '/Product/AddProduct',
        type: 'POST',
        contentType: false,
        processData: false,
        data: formData,
        success: window.location.href = "/Product/ProductList"

    });

```

答:
When you use ajax you can not redirect on the server side. So you have two choices

Add form and  submit button to your view, and use it to submit data. In this case you will be redirected from action on the server side.

Or fix your  succes ajax part

```<code>
success: window.location.href='@Url.Action("ProductList","Product")';

//or

success:  window.location.href = "/product/productList" ;
</code>```

Body:
When you use ajax you can not redirect on the server side. So you have two choices

Add form and  submit button to your view, and use it to submit data. In this case you will be redirected from action on the server side.

Or fix your  succes ajax part

success: window.location.href='@Url.Action("ProductList","Product")';

//or

success:  window.location.href = "/product/productList" ;


问:
How to capture prints in real time from function?
I want to capture all the prints and do something like return them but keep running the function.
I found this method but it only returns the prints when the code is finished.

```<code>f = io.StringIO()
with redirect_stdout(f):
    # my code

return f.getvalue()
</code>```

Is there any method to capture every print in real-time?

答:
You could write your own file-like object that processes lines of text as it sees them. In the simplest case you only need to supply a write method as shown below. The tricky part is knowing when a "print" call is done. print may call stdout.write several times to do a single print operation. In this example, I did processing whenever a newline is seen. This code does not return interim prints but does allow you to intercept the writes to stdout and process them before returning to the function that calls print.

```<code>from contextlib import redirect_stdout
import sys

real_stdout_for_test = sys.stdout

class WriteProcessor:

    def __init__(self):
        self.buf = ""

    def write(self, buf):
        # emit on each newline
        while buf:
            try:
                newline_index = buf.index("\n")
            except ValueError:
                # no newline, buffer for next call
                self.buf += buf
                break
            # get data to next newline and combine with any buffered data
            data = self.buf + buf[:newline_index + 1]
            self.buf = ""
            buf = buf[newline_index + 1:]
            # perform complex calculations... or just print with a note.
            real_stdout_for_test.write("fiddled with " + data)

with redirect_stdout(WriteProcessor()):
    print("hello there")
    print("a\nprint\nof\nmany\nlines")
    print("goodbye ", end="")
    print("for now")
</code>```

Body:
I want to capture all the prints and do something like return them but keep running the function.
I found this method but it only returns the prints when the code is finished.

f = io.StringIO()
with redirect_stdout(f):
    # my code

return f.getvalue()

Is there any method to capture every print in real-time?

Body:
You could write your own file-like object that processes lines of text as it sees them. In the simplest case you only need to supply a write method as shown below. The tricky part is knowing when a "print" call is done. print may call stdout.write several times to do a single print operation. In this example, I did processing whenever a newline is seen. This code does not return interim prints but does allow you to intercept the writes to stdout and process them before returning to the function that calls print.

from contextlib import redirect_stdout
import sys

real_stdout_for_test = sys.stdout

class WriteProcessor:

    def __init__(self):
        self.buf = ""

    def write(self, buf):
        # emit on each newline
        while buf:
            try:
                newline_index = buf.index("\n")
            except ValueError:
                # no newline, buffer for next call
                self.buf += buf
                break
            # get data to next newline and combine with any buffered data
            data = self.buf + buf[:newline_index + 1]
            self.buf = ""
            buf = buf[newline_index + 1:]
            # perform complex calculations... or just print with a note.
            real_stdout_for_test.write("fiddled with " + data)

with redirect_stdout(WriteProcessor()):
    print("hello there")
    print("a\nprint\nof\nmany\nlines")
    print("goodbye ", end="")
    print("for now")


问:
alternative to an if else inside a for loop
Currently my code is working properly but I would like to have an alternate version of my code example without the if else. I would like to use functional programming with functions such as find but I am a bit stuck with the second loop inside the first loop.

The aim of the code is to update the array of tasks when either a task or a subTask when their id matches a given id. The updated task (or subTask) should remain at the same position in the array of tasks (or in the array of subTasks)

To simply things:

```<code>const idToSearch; let index; let subIndex;
for (const task of tasks){
  if (task.id === idToSearch && stuff to validate task){
     const task = update(task, parameters);
     tasks.splice(index, 1, task);
  }
  else if (task.hasSubTasks){
     for (const subTask of task.subTasks){
        if (subTask.id = idToSearch && stuff to validate subtask){
           const subTask = update(subTask, parameters);
           task.subTasks.splice(subIndex, 1, subTask);
           tasks.splice(index,1,task);
           return;
        }
        subIndex++;
     }

 }
 index++;
}
</code>```

Could you give me some pointers?

Edit: I just reformulated the question, I was not precise enough.

Edit2: the update function is pure. It takes the task (or subTask) and parameters and returns the updated task / subTask.

答:
How about something like this?

const updateById = (id) => {
    let task = tasks.find(t => t.id == id);
    if (task) {
        return update(task);
    }
    task = tasks.map(t => t.subTasks).flat().find(t => t.id == id);
    if (task) {
        return update(task);
    }
}
```
<p>This code uses the <code>find</code> function, checking the id against the task updating the first it finds. If it can't find it, it uses the <code>map</code> function to return all the subtasks of each (making an array of arrays), then calls <code>flat()</code> on it to make it one continuous array before using find as above.</p>
<h2>Edit:</h2>
<p>You keep asking about returning or updating the array. You don't need to, as JavaScript objects only exist in one location. To update them in one place updates them everywhere, unless you break the reference to them.</p>
<p>Here is an example where I update one <code>task</code> but logging the original tasks array shows the newly updated task.</p>
<p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
<div class="snippet-code">
<pre class="snippet-code-js lang-js prettyprint-override"><code>const tasks = [
  {
    id: 1,
    name: "one",
    val: 1,
    subtasks: []
  },
  {
    id: 2,
    name: "two",
    val: 2,
    subtasks: []
  },
  {
    id: 3,
    name: "three",
    val: 3,
    subtasks: [{
      id: 4,
      name: "four",
      val: 4,
    }]
  },
];

const update = (task) => {
  task.val += 10;
}

const updateById = (id) => {
  let task = tasks.find(t => t.id == id);
  if (task) {
    update(task);
  }
  task = tasks.map(t => t.subtasks).flat().find(t => t.id == id);
  if (task) {
    update(task);
  }
}

updateById(4);

// Prints tasks and also shows task id = 4 with a value of 14
console.log(tasks);</code>```

Note that the task with an ID of 4 which is updated is modified in the tasks array.

If you are asking something different (which I think you might be) along the lines of:

How can I create a new array with the modified data in it, then the code will be almost the same, but we must clone the array at the start of our function.

Please note I have added a tasks argument to the function.

const tasks = [
  {
    id: 1,
    name: "one",
    val: 1,
    subtasks: []
  },
  {
    id: 2,
    name: "two",
    val: 2,
    subtasks: []
  },
  {
    id: 3,
    name: "three",
    val: 3,
    subtasks: [{
      id: 4,
      name: "four",
      val: 4,
    }]
  },
];

const update = (task) => {
  task.val += 10;
}

const updateById = (tasks, id) => {
  // Perform a deep clone of the array. There are many ways to do this, and much better stackoverflow answers than mine on how they work
  // It is this action that "breaks the reference" to the original array and its objects
  tasks = JSON.parse(JSON.stringify(tasks));
  let task = tasks.find(t => t.id == id);
  if (task) {
    update(task);
    return tasks;
  }
  task = tasks.map(t => t.subtasks).flat().find(t => t.id == id);
  if (task) {
    update(task);
    return tasks;
  }
}

const newTasks = updateById(tasks, 4);

console.log({newTasks}, {oldTasks: tasks});```

In this example, the newTasks is updated while leaving the original tasks intact.

Body:
Currently my code is working properly but I would like to have an alternate version of my code example without the if else. I would like to use functional programming with functions such as find but I am a bit stuck with the second loop inside the first loop.

The aim of the code is to update the array of tasks when either a task or a subTask when their id matches a given id. The updated task (or subTask) should remain at the same position in the array of tasks (or in the array of subTasks)

To simply things:

const idToSearch; let index; let subIndex;
for (const task of tasks){
  if (task.id === idToSearch && stuff to validate task){
     const task = update(task, parameters);
     tasks.splice(index, 1, task);
  }
  else if (task.hasSubTasks){
     for (const subTask of task.subTasks){
        if (subTask.id = idToSearch && stuff to validate subtask){
           const subTask = update(subTask, parameters);
           task.subTasks.splice(subIndex, 1, subTask);
           tasks.splice(index,1,task);
           return;
        }
        subIndex++;
     }

 }
 index++;
}

Could you give me some pointers?

Edit: I just reformulated the question, I was not precise enough.

Edit2: the update function is pure. It takes the task (or subTask) and parameters and returns the updated task / subTask.

Body:
How about something like this?

const updateById = (id) => {
    let task = tasks.find(t => t.id == id);
    if (task) {
        return update(task);
    }
    task = tasks.map(t => t.subTasks).flat().find(t => t.id == id);
    if (task) {
        return update(task);
    }
}

This code uses the find function, checking the id against the task updating the first it finds. If it can't find it, it uses the map function to return all the subtasks of each (making an array of arrays), then calls flat() on it to make it one continuous array before using find as above.

Edit:

You keep asking about returning or updating the array. You don't need to, as JavaScript objects only exist in one location. To update them in one place updates them everywhere, unless you break the reference to them.

Here is an example where I update one task but logging the original tasks array shows the newly updated task.

const tasks = [
  {
    id: 1,
    name: "one",
    val: 1,
    subtasks: []
  },
  {
    id: 2,
    name: "two",
    val: 2,
    subtasks: []
  },
  {
    id: 3,
    name: "three",
    val: 3,
    subtasks: [{
      id: 4,
      name: "four",
      val: 4,
    }]
  },
];

const update = (task) => {
  task.val += 10;
}

const updateById = (id) => {
  let task = tasks.find(t => t.id == id);
  if (task) {
    update(task);
  }
  task = tasks.map(t => t.subtasks).flat().find(t => t.id == id);
  if (task) {
    update(task);
  }
}

updateById(4);

// Prints tasks and also shows task id = 4 with a value of 14
console.log(tasks);

Note that the task with an ID of 4 which is updated is modified in the tasks array.

If you are asking something different (which I think you might be) along the lines of:

How can I create a new array with the modified data in it, then the code will be almost the same, but we must clone the array at the start of our function.

Please note I have added a tasks argument to the function.

const tasks = [
  {
    id: 1,
    name: "one",
    val: 1,
    subtasks: []
  },
  {
    id: 2,
    name: "two",
    val: 2,
    subtasks: []
  },
  {
    id: 3,
    name: "three",
    val: 3,
    subtasks: [{
      id: 4,
      name: "four",
      val: 4,
    }]
  },
];

const update = (task) => {
  task.val += 10;
}

const updateById = (tasks, id) => {
  // Perform a deep clone of the array. There are many ways to do this, and much better stackoverflow answers than mine on how they work
  // It is this action that "breaks the reference" to the original array and its objects
  tasks = JSON.parse(JSON.stringify(tasks));
  let task = tasks.find(t => t.id == id);
  if (task) {
    update(task);
    return tasks;
  }
  task = tasks.map(t => t.subtasks).flat().find(t => t.id == id);
  if (task) {
    update(task);
    return tasks;
  }
}

const newTasks = updateById(tasks, 4);

console.log({newTasks}, {oldTasks: tasks});

In this example, the newTasks is updated while leaving the original tasks intact.


问:
Postgres: select all records in 95th percentile
I'm trying to query all rows which are in the 95th percentile. Here's what I tried.

select
  id,
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage
where usage > percentile_95
```

答:
Your query fails with ERROR: column "percentile_95" does not exist which is caused by the fact that you can't reference in the WHERE clause column alias from the SELECT.

If you remove the WHERE clause you get a different error

```<code>select
  id,
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage

ERROR: column "resourceusage.id" must appear in the GROUP BY clause or be used in an aggregate function
</code>```

Which points you how to calculate the percentile_cont for the whole table (i.e. without using GROUP BY):

```<code>select
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage;

percentile_95
-------------------------
95.05
</code>```

Now you are close to the result, get the above result in a subquery and use it in the WHERE clause.

```<code>select
  id,
  usage
from ResourceUsage
where usage >
 (select
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
 from ResourceUsage);
</code>```

Body:
Your query fails with ERROR: column "percentile_95" does not exist which is caused by the fact that you can't reference in the WHERE clause column alias from the SELECT.

If you remove the WHERE clause you get a different error

select
  id,
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage

ERROR: column "resourceusage.id" must appear in the GROUP BY clause or be used in an aggregate function

Which points you how to calculate the percentile_cont for the whole table (i.e. without using GROUP BY):

select
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
from ResourceUsage;

percentile_95
-------------------------
95.05

Now you are close to the result, get the above result in a subquery and use it in the WHERE clause.

select
  id,
  usage
from ResourceUsage
where usage >
 (select
  percentile_cont(0.95) within group (order by usage asc) as percentile_95
 from ResourceUsage);


问:
R Residuals from predicted fit?
I understand that when you generate a linear model, you can pull the residuals from the fit like this:

```<code># Model
model <- lm(y ~ x, data = coolstuff)

# Residuals
myresids <- model$residuals
</code>```

I understand further that you can use this model to predict values based on a second data set (e.g., a validation data set) like this:

```<code>mypreds <- predict(model, newdata = coolvalid)
</code>```

Where I'm lost is where I can find the residuals from the prediction. predict doesn't generate a data frame or a tibble - just a named list of numbers.

Where can I find the residuals from the predictions?

答:
Exactly as DanY points out in the comments, the residual is simply the observed value - the predicted value. See below for simple example with built in data.

# sample data
set.seed(1)
split_indicies <- sample(nrow(mtcars), nrow(mtcars)/2)
train <- mtcars[split_indicies,]
test <- mtcars[-split_indicies,]

# model
model <- lm(mpg ~ disp, data = train)

# residuals of prediction are actual - predicted
test$mpg - predict(model, data = test)
#> Pontiac Firebird   Hornet 4 Drive       Duster 360        Mazda RX4
#>        9.8318659       -0.1503095        3.4749151        1.4901610
#>    Mazda RX4 Wag      AMC Javelin        Merc 280C      Merc 450SLC
#>       -0.1098390       -0.5448161       -5.2950183       -7.7129664
#>         Fiat 128      Honda Civic   Ford Pantera L    Toyota Corona
#>      -15.8775915      -11.7018628        0.5021011      -11.2626474
#>         Merc 280       Volvo 142E   Toyota Corolla     Ferrari Dino
#>        4.7049817        1.4746340        3.8075878       -8.5311955
```

Created on 2022-02-07 by the reprex package (v2.0.1)

Title: R Residuals from predicted fit?

Body:
I understand that when you generate a linear model, you can pull the residuals from the fit like this:

# Model
model <- lm(y ~ x, data = coolstuff)

# Residuals
myresids <- model$residuals

I understand further that you can use this model to predict values based on a second data set (e.g., a validation data set) like this:

mypreds <- predict(model, newdata = coolvalid)

Where I'm lost is where I can find the residuals from the prediction. predict doesn't generate a data frame or a tibble - just a named list of numbers.

Where can I find the residuals from the predictions?

Body:
Exactly as DanY points out in the comments, the residual is simply the observed value - the predicted value. See below for simple example with built in data.

# sample data
set.seed(1)
split_indicies <- sample(nrow(mtcars), nrow(mtcars)/2)
train <- mtcars[split_indicies,]
test <- mtcars[-split_indicies,]

# model
model <- lm(mpg ~ disp, data = train)

# residuals of prediction are actual - predicted
test$mpg - predict(model, data = test)
#> Pontiac Firebird   Hornet 4 Drive       Duster 360        Mazda RX4
#>        9.8318659       -0.1503095        3.4749151        1.4901610
#>    Mazda RX4 Wag      AMC Javelin        Merc 280C      Merc 450SLC
#>       -0.1098390       -0.5448161       -5.2950183       -7.7129664
#>         Fiat 128      Honda Civic   Ford Pantera L    Toyota Corona
#>      -15.8775915      -11.7018628        0.5021011      -11.2626474
#>         Merc 280       Volvo 142E   Toyota Corolla     Ferrari Dino
#>        4.7049817        1.4746340        3.8075878       -8.5311955

Created on 2022-02-07 by the reprex package (v2.0.1)


问:
Microsoft Sentinel searching for a string in raw payload
I'm trying to find out if there is a way in Microsoft Sentinel to perform a search across all Data Connectors and all logs.
This is a similar functionality to that provided by, for example, IBM QRadar, where it is possible to perform a search across all Log Sources and a payload overview of all saved logs in their raw syslog form.

AQL query format in QRadar that I would like to do in Sentinel:

SELECT UTF8(payload) FROM events WHERE UTF8(payload) ILIKE 'search string' LAST 24 HOURS

This is a very useful feature, especially when back-analyzing logs and debugging rules.

Thank you.

答:
You can use the search operator.  search operator is substantially less efficient than table-specific and column-specific text filtering.

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/searchoperator?pivots=azuremonitor


问:
Generating ellipse with rotation and semi-major and semi-minor axes
I have the parameters of an eclipse: semi-major axis; semi-minor axis; rotation angle; x-position of the centre of the ellipse; y-position of the centre of the ellipse.

I wish to produce an array whereby all points inside the ellipse are set to one and all points outside are zero.

I found this answer, which gave a solution to this without rotation, and using height and width, rather than semi-major and semi-minor:

import numpy as np
from matplotlib import pyplot as plt

x0 = 4  # x center,
a = 5  #  half width
y0 = 2  # y center,
b = 3  # half height
x = np.linspace(-10, 10, 100)  # x values of interest
y = np.linspace(-5, 5, 100)[:,
                            None]  # y values of interest, as a "column" array
ellipse = ((x - x0) / a)**2 + (
    (y - y0) / b)**2 <= 1  # True for points inside the ellipse

plt.imshow(ellipse, origin="lower")  # Plot
```
<p><img src="https://i.stack.imgur.com/0gfTz.png" alt="non-rotated ellipse" /></p>
<p>Thus, I'm looking to modify this code to take the parameters I require.  I found a definition of the general equation of an eclipse here</p>
<p><img src="https://i.stack.imgur.com/7qy46.png" alt="enter image description here" /></p>
<p>I attempted to reproduce this with this modification:</p>
<pre class="lang-py prettyprint-override"><code>import numpy as np
from matplotlib import pyplot as plt

x0 = 4  # x center,
a = 5  #  half width
y0 = 2  # y center,
b = 3  # half height
x = np.linspace(-10, 10, 100)  # x values of interest
y = np.linspace(-5, 5, 100)[:,
                            None]  # y values of interest, as a "column" array

def calc_ellipse(x, y, x_centre, y_centre, rotation, semi_major, semi_minor):
    # https://www.maa.org/external_archive/joma/Volume8/Kalman/General.html
    ...
    term1 = ((x - x_centre) * np.cos(rotation)) + (
        (y - y0) * np.sin(rotation))**2
    term2 = ((x - x_centre) * np.sin(rotation)) + (
        (y - y0) * np.cos(rotation))**2
    ellipse = ((term1 / semi_major**2) + (term2 / semi_minor**2)) <= 1
    return ellipse

ellipse = calc_ellipse(x, y, x0, y0, 0, a, b)

plt.imshow(ellipse, origin="lower")  # Plot
</code>```

However as you can see I end up with a strange shape, even when I set the rotation angle to 0.

This question showed a way of generating an ellipse with a rotation, however the parameters are hard-coded, and thus is not reuseable or easy to interpret.

How would I generate an array like this with the parameters defining the general equation of the eclipse?

答:
In your calc_ellipse function you are missing parenthesis in term1 and term2. To match the formula you've posted it should look like this:

```<code>    term1 = (((x - x_centre) * np.cos(rotation)) + (
        (y - y0) * np.sin(rotation)))**2
    term2 = (((x - x_centre) * np.sin(rotation)) + (
        (y - y0) * np.cos(rotation)))**2

</code>```

Or like this:

```<code>    term1 = ((x - x_centre) * np.cos(rotation) +
        (y - y0) * np.sin(rotation))**2
    term2 = ((x - x_centre) * np.sin(rotation) +
        (y - y0) * np.cos(rotation))**2

</code>```

Try those changes and see if it works.

Body:
I have the parameters of an eclipse: semi-major axis; semi-minor axis; rotation angle; x-position of the centre of the ellipse; y-position of the centre of the ellipse.

I wish to produce an array whereby all points inside the ellipse are set to one and all points outside are zero.

I found this answer, which gave a solution to this without rotation, and using height and width, rather than semi-major and semi-minor:

import numpy as np
from matplotlib import pyplot as plt

x0 = 4  # x center,
a = 5  #  half width
y0 = 2  # y center,
b = 3  # half height
x = np.linspace(-10, 10, 100)  # x values of interest
y = np.linspace(-5, 5, 100)[:,
                            None]  # y values of interest, as a "column" array
ellipse = ((x - x0) / a)**2 + (
    (y - y0) / b)**2 <= 1  # True for points inside the ellipse

plt.imshow(ellipse, origin="lower")  # Plot

Thus, I'm looking to modify this code to take the parameters I require.  I found a definition of the general equation of an eclipse here

I attempted to reproduce this with this modification:

import numpy as np
from matplotlib import pyplot as plt

x0 = 4  # x center,
a = 5  #  half width
y0 = 2  # y center,
b = 3  # half height
x = np.linspace(-10, 10, 100)  # x values of interest
y = np.linspace(-5, 5, 100)[:,
                            None]  # y values of interest, as a "column" array

def calc_ellipse(x, y, x_centre, y_centre, rotation, semi_major, semi_minor):
    # https://www.maa.org/external_archive/joma/Volume8/Kalman/General.html
    ...
    term1 = ((x - x_centre) * np.cos(rotation)) + (
        (y - y0) * np.sin(rotation))**2
    term2 = ((x - x_centre) * np.sin(rotation)) + (
        (y - y0) * np.cos(rotation))**2
    ellipse = ((term1 / semi_major**2) + (term2 / semi_minor**2)) <= 1
    return ellipse

ellipse = calc_ellipse(x, y, x0, y0, 0, a, b)

plt.imshow(ellipse, origin="lower")  # Plot

However as you can see I end up with a strange shape, even when I set the rotation angle to 0.

This question showed a way of generating an ellipse with a rotation, however the parameters are hard-coded, and thus is not reuseable or easy to interpret.

How would I generate an array like this with the parameters defining the general equation of the eclipse?

Body:
In your calc_ellipse function you are missing parenthesis in term1 and term2. To match the formula you've posted it should look like this:

    term1 = (((x - x_centre) * np.cos(rotation)) + (
        (y - y0) * np.sin(rotation)))**2
    term2 = (((x - x_centre) * np.sin(rotation)) + (
        (y - y0) * np.cos(rotation)))**2

Or like this:

    term1 = ((x - x_centre) * np.cos(rotation) +
        (y - y0) * np.sin(rotation))**2
    term2 = ((x - x_centre) * np.sin(rotation) +
        (y - y0) * np.cos(rotation))**2

Try those changes and see if it works.


问:
How to extract a table from a website using BeautifulSoup?
I'm wanting to extract the FIPS code for each county in Louisiana from this website using beautiful soup and create a Pandas Dataframe: https://www.nrcs.usda.gov/wps/portal/nrcs/detail/la/technical/cp/?cid=nrcs143_013697

The columns would be FIPS, Name, and State. I've tried finding by tr, td, and table when I inspect the element, but I don't know how to single out just the main data and then put it into a pandas dataframe. Once I find the specific table, it should be easy to do something like:

if state == 'LA':
     # put data into a dataframe
```
<pre class="lang-py prettyprint-override"><code>
import requests
from bs4 import BeautifulSoup

url = "https://www.nrcs.usda.gov/wps/portal/nrcs/detail/la/technical/cp/?cid=nrcs143_013697"
html_text = requests.get(url).text
soup = BeautifulSoup(html_text, 'html.parser')
# print(soup)
for county in soup.find_all('table'):
    print(county.text)
</code>```

答:
There is one table so can iterate over the
 elements in that one table.

If want a data frame to include only one particular state then can filter it before adding to a data frame, or filter the data frame of all data for a subset data frame.

```<code>import requests
from bs4 import BeautifulSoup
import pandas as pd

url = "https://www.nrcs.usda.gov/wps/portal/nrcs/detail/la/technical/cp/?cid=nrcs143_013697"
html_text = requests.get(url).text
soup = BeautifulSoup(html_text, 'html.parser')
data = []
for tr in soup.find('table', class_='data').find_all('tr'):
    row = [td.text for td in tr.find_all('td')]
    # If want to filter out all except LA then can do that here
    if len(row) == 3 and row[2] == 'LA':
        data.append(row)
df = pd.DataFrame(data, columns=['FIPS', 'Name', 'State'])
print(df)
</code>```

Output:

```<code>     FIPS          Name State
0   22001        Acadia    LA
1   22003         Allen    LA
2   22005     Ascension    LA
3   22007    Assumption    LA
4   22009     Avoyelles    LA
..    ...           ...   ...
63  22127          Winn    LA
</code>```

Body:
I'm wanting to extract the FIPS code for each county in Louisiana from this website using beautiful soup and create a Pandas Dataframe: https://www.nrcs.usda.gov/wps/portal/nrcs/detail/la/technical/cp/?cid=nrcs143_013697

The columns would be FIPS, Name, and State. I've tried finding by tr, td, and table when I inspect the element, but I don't know how to single out just the main data and then put it into a pandas dataframe. Once I find the specific table, it should be easy to do something like:

if state == 'LA':
     # put data into a dataframe

import requests
from bs4 import BeautifulSoup

url = "https://www.nrcs.usda.gov/wps/portal/nrcs/detail/la/technical/cp/?cid=nrcs143_013697"
html_text = requests.get(url).text
soup = BeautifulSoup(html_text, 'html.parser')
# print(soup)
for county in soup.find_all('table'):
    print(county.text)

Body:
There is one table so can iterate over the <tr> elements in that one table.

If want a data frame to include only one particular state then can filter it before adding to a data frame, or filter the data frame of all data for a subset data frame.

import requests
from bs4 import BeautifulSoup
import pandas as pd

url = "https://www.nrcs.usda.gov/wps/portal/nrcs/detail/la/technical/cp/?cid=nrcs143_013697"
html_text = requests.get(url).text
soup = BeautifulSoup(html_text, 'html.parser')
data = []
for tr in soup.find('table', class_='data').find_all('tr'):
    row = [td.text for td in tr.find_all('td')]
    # If want to filter out all except LA then can do that here
    if len(row) == 3 and row[2] == 'LA':
        data.append(row)
df = pd.DataFrame(data, columns=['FIPS', 'Name', 'State'])
print(df)

Output:

     FIPS          Name State
0   22001        Acadia    LA
1   22003         Allen    LA
2   22005     Ascension    LA
3   22007    Assumption    LA
4   22009     Avoyelles    LA
..    ...           ...   ...
63  22127          Winn    LA


问:
metpy cross_section function does not recognize coordinates in ERA5
I use the ERA5 dataset in order to plot a cross section of vertical velocity over southern Africa. A sample of the data is found here. I try to replicate the example given here, however, the cross_section function does not work, as there is missing information in the dataset's coordinates.
The code I use is the following:

```<code>from matplotlib import pyplot
from matplotlib.cm import get_cmap
from __future__ import print_function
from netCDF4 import Dataset,num2date,date2num
from matplotlib.colors import from_levels_and_colors
from cartopy import crs
from cartopy.feature import NaturalEarthFeature, COLORS
from metpy.units import units
from datetime import datetime
from metpy.plots import StationPlot
from metpy.interpolate import cross_section
from PIL import Image, ImageDraw
from mpl_toolkits.basemap import Basemap
from mpl_toolkits.axes_grid1 import ImageGrid
#
import os
import netCDF4 as nc
import scipy.spatial as ss
import matplotlib.pyplot as plt
import metpy.calc as mpcalc
import xarray as xr
import cartopy.crs as ccrs
import matplotlib
import numpy as np
import datetime
import cartopy.feature as cfeature
import matplotlib.gridspec as gridspec
import scipy.ndimage as ndimage
#
root_dir = '/users/.../my_var/'
#
data = xr.open_dataset('omega.nc')
data = data.metpy.parse_cf().squeeze()
print(data)
#
</code>```

The data are the following:

```<code><xarray.Dataset>
Dimensions:    (bnds: 2, latitude: 161, level: 16, longitude: 137)
Coordinates:
    time       datetime64[ns] 2005-02-01
  * longitude  (longitude) float32 8.0 8.25 8.5 8.75 ... 41.25 41.5 41.75 42.0
  * latitude   (latitude) float32 -5.0 -5.25 -5.5 -5.75 ... -44.5 -44.75 -45.0
  * level      (level) float64 100.0 150.0 200.0 250.0 ... 750.0 800.0 850.0
    metpy_crs  object Projection: latitude_longitude
Dimensions without coordinates: bnds
Data variables:
    time_bnds  (bnds) datetime64[ns] 2005-01-01 2005-12-01
    w          (level, latitude, longitude) float32 -0.001213 ... 0.003879
Attributes:
    CDI:          Climate Data Interface version 1.9.7.1 (http://mpimet.mpg.d...
    Conventions:  CF-1.6
    history:      Fri Feb 04 23:12:51 2022: cdo yearmean omega_SAF_DJF_1986_2...
    frequency:    year
    CDO:          Climate Data Operators version 1.9.7.1 (http://mpimet.mpg.d...
</code>```

Proceeding on with providing the coordinates over which I want the cross section:

```<code>start = (-7, 17)
end = (-28, 17)
cross = cross_section(data, start, end).set_coords(('latitude', 'longitude'))
</code>```

Eventually the cross_section function yields the following error message, related to missing information about ERA5's coordinates:

```<code>Traceback (most recent call last):
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/interpolate/slices.py", line 167, in cross_section
    x = data.metpy.x
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/xarray.py", line 431, in x
    return self._axis('x')
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/xarray.py", line 385, in _axis
    raise AttributeError(axis + ' attribute is not available.')
AttributeError: x attribute is not available.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/interpolate/slices.py", line 157, in cross_section
    interp_type=interp_type)
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/xarray/core/dataset.py", line 4592, in map
    for k, v in self.data_vars.items()
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/xarray/core/dataset.py", line 4592, in <dictcomp>
    for k, v in self.data_vars.items()
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/interpolate/slices.py", line 169, in cross_section
    raise ValueError('Data missing required coordinate information. Verify that '
ValueError: Data missing required coordinate information. Verify that your data have been parsed by MetPy with proper x and y dimension coordinates and added crs coordinate of the correct projection for each variable.
</code>```

Any help on what a work around would be?

Thank you!

答:
As can be seen in your dataset output, there is a variable (time_bnds) which does not have horizontal coordinate variables, which are required for every data variable in a Dataset passed to metpy.interpolate.cross_section (as can be seen in the documentation). So, we have to exclude such data variables before using cross_section somehow.

A simple fix for such a "coordinate-like" variable as time_bnds would be to just promote it to an auxiliary coordinate, like so:

data = xr.open_dataset('omega.nc')
data = data.metpy.parse_cf().squeeze().set_coords('time_bnds')

#####

start = (-7, 17)
end = (-28, 17)
cross = cross_section(data, start, end).set_coords(('latitude', 'longitude'))
```

This is also the same issue as https://github.com/Unidata/MetPy/discussions/2033, so feel free to read that discussion for more information and ideas on how this could be improved in MetPy in the future!

Body:
I use the ERA5 dataset in order to plot a cross section of vertical velocity over southern Africa. A sample of the data is found here. I try to replicate the example given here, however, the cross_section function does not work, as there is missing information in the dataset's coordinates.
The code I use is the following:

from matplotlib import pyplot
from matplotlib.cm import get_cmap
from __future__ import print_function
from netCDF4 import Dataset,num2date,date2num
from matplotlib.colors import from_levels_and_colors
from cartopy import crs
from cartopy.feature import NaturalEarthFeature, COLORS
from metpy.units import units
from datetime import datetime
from metpy.plots import StationPlot
from metpy.interpolate import cross_section
from PIL import Image, ImageDraw
from mpl_toolkits.basemap import Basemap
from mpl_toolkits.axes_grid1 import ImageGrid
#
import os
import netCDF4 as nc
import scipy.spatial as ss
import matplotlib.pyplot as plt
import metpy.calc as mpcalc
import xarray as xr
import cartopy.crs as ccrs
import matplotlib
import numpy as np
import datetime
import cartopy.feature as cfeature
import matplotlib.gridspec as gridspec
import scipy.ndimage as ndimage
#
root_dir = '/users/.../my_var/'
#
data = xr.open_dataset('omega.nc')
data = data.metpy.parse_cf().squeeze()
print(data)
#

The data are the following:

<xarray.Dataset>
Dimensions:    (bnds: 2, latitude: 161, level: 16, longitude: 137)
Coordinates:
    time       datetime64[ns] 2005-02-01
  * longitude  (longitude) float32 8.0 8.25 8.5 8.75 ... 41.25 41.5 41.75 42.0
  * latitude   (latitude) float32 -5.0 -5.25 -5.5 -5.75 ... -44.5 -44.75 -45.0
  * level      (level) float64 100.0 150.0 200.0 250.0 ... 750.0 800.0 850.0
    metpy_crs  object Projection: latitude_longitude
Dimensions without coordinates: bnds
Data variables:
    time_bnds  (bnds) datetime64[ns] 2005-01-01 2005-12-01
    w          (level, latitude, longitude) float32 -0.001213 ... 0.003879
Attributes:
    CDI:          Climate Data Interface version 1.9.7.1 (http://mpimet.mpg.d...
    Conventions:  CF-1.6
    history:      Fri Feb 04 23:12:51 2022: cdo yearmean omega_SAF_DJF_1986_2...
    frequency:    year
    CDO:          Climate Data Operators version 1.9.7.1 (http://mpimet.mpg.d...

Proceeding on with providing the coordinates over which I want the cross section:

start = (-7, 17)
end = (-28, 17)
cross = cross_section(data, start, end).set_coords(('latitude', 'longitude'))

Eventually the cross_section function yields the following error message, related to missing information about ERA5's coordinates:

Traceback (most recent call last):
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/interpolate/slices.py", line 167, in cross_section
    x = data.metpy.x
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/xarray.py", line 431, in x
    return self._axis('x')
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/xarray.py", line 385, in _axis
    raise AttributeError(axis + ' attribute is not available.')
AttributeError: x attribute is not available.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/interpolate/slices.py", line 157, in cross_section
    interp_type=interp_type)
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/xarray/core/dataset.py", line 4592, in map
    for k, v in self.data_vars.items()
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/xarray/core/dataset.py", line 4592, in <dictcomp>
    for k, v in self.data_vars.items()
  File "/users/pr007/mkaryp/.local/lib/python3.6/site-packages/metpy/interpolate/slices.py", line 169, in cross_section
    raise ValueError('Data missing required coordinate information. Verify that '
ValueError: Data missing required coordinate information. Verify that your data have been parsed by MetPy with proper x and y dimension coordinates and added crs coordinate of the correct projection for each variable.

Any help on what a work around would be?

Thank you!

Body:
As can be seen in your dataset output, there is a variable (time_bnds) which does not have horizontal coordinate variables, which are required for every data variable in a Dataset passed to metpy.interpolate.cross_section (as can be seen in the documentation). So, we have to exclude such data variables before using cross_section somehow.

A simple fix for such a "coordinate-like" variable as time_bnds would be to just promote it to an auxiliary coordinate, like so:

data = xr.open_dataset('omega.nc')
data = data.metpy.parse_cf().squeeze().set_coords('time_bnds')

#####

start = (-7, 17)
end = (-28, 17)
cross = cross_section(data, start, end).set_coords(('latitude', 'longitude'))

This is also the same issue as https://github.com/Unidata/MetPy/discussions/2033, so feel free to read that discussion for more information and ideas on how this could be improved in MetPy in the future!


问:
pass props to styled-components selector
I've got a styled-component which gets some additional styles based on a property (active).

The component looks similar to this:

import styled from 'styled-components';

const Button = styled.button`
  color: black;

  ${props => props.active ? `color: red;` : ''}
`;
```
<p>Within a component test I need to select the active Button which (obviously) doesn't work doing the following:</p>
<pre class="lang-js prettyprint-override"><code>document.querySelector(Button)
</code>```

since this targets all Button components, no matter if active or not.

I read the styled-components docs and googled a lot. However I haven't been able to find a way to pass specific props to the styled-components-selector. I expected something similar to the following (which does not work)

document.querySelector(Button({ active: true }))
```

Is there any way to achieve this or rather how do you select a styled component which has specific props?

答:
The correct syntax is this:</p>
```<code>const Button = styled.button`
  color: black;
  color: ${props => props.active ? 'red' : ''};
`;
</code>```

Body:
I've got a styled-component which gets some additional styles based on a property (active).

The component looks similar to this:

import styled from 'styled-components';

const Button = styled.button`
  color: black;

  ${props => props.active ? `color: red;` : ''}
`;

Within a component test I need to select the active Button which (obviously) doesn't work doing the following:

document.querySelector(Button)

since this targets all Button components, no matter if active or not.

I read the styled-components docs and googled a lot. However I haven't been able to find a way to pass specific props to the styled-components-selector. I expected something similar to the following (which does not work)

document.querySelector(Button({ active: true }))

Is there any way to achieve this or rather how do you select a styled component which has specific props?

Body:
The correct syntax is this:

const Button = styled.button`
  color: black;
  color: ${props => props.active ? 'red' : ''};
`;


问:
Why is my use of exception capturing not good?
```<code>def parse_input(json_str):
    json_dict = {'input': []}
    try:
        json_dict = json.loads(json_str)
        if 'input' in json_dict:
            json_dict['input'] = json.loads(json_dict['input'])
    except JSONDecodeError as e:
        print(f'Invalid json str format: {json_str} {e}')
    except TypeError as e:
        print(f'json_str is not a string: {json_str} {e}')

    return json_dict
</code>```

This function is to parse json string and ALWAYS produce a valid dict object, even if an exception occurs. However, when I process a batch of json strings, it still fails on one of the json strings, and the error is below:

```<code>json_str is not a string: null argument of type 'NoneType' is not iterable
Traceback (most recent call last):
  File "kafka_consumer.py", line 76, in <module>
    fetch_data(sys.argv[1])
  File "kafka_consumer.py", line 55, in fetch_data
    for input in json_dict['input']:
TypeError: 'NoneType' object is not subscriptable
</code>```

The code produced the error above:

```<code>        json_dict = parse_input(data)
        for input in json_dict['input']:
            unix_time = input['time']
            ...
</code>```

So the question is, why doesn't the parse_input() produce a valid dict when the exception occurs? Is my way of using the exception capturing wrong?

答:
This exact error message happens when data == 'null':

```<code>>>> json_dict = parse_input('null')
json_str is not a string: null argument of type 'NoneType' is not iterable
>>> type(json_dict)
<class 'NoneType'>
</code>```

In this case:

json.loads('null') returns None, so that json_dict is None

'input' in json_dict attempts to iterate over None, but that's an error

Python (not the JSON library!) thus throws the TypeError

Your code handles it and runs print(f'json_str is not a string: {json_str} {e}'). However, the error happened not because json_str wasn't a string, since 'null' is a (Python) string.

Finally, json_dict['input'] attempts to index into the None value, which is an error as well.

Similar behavior can happen when data == '{"input": "null"}':

```<code>>>> parse_input('{"input": "null"}')
{'input': None}
</code>```

This is caused by this line: json_dict['input'] = json.loads(json_dict['input']). It parses the string "null" as the Python value None which for input in json_dict['input'] attempts to iterate over, but that's basically for input in None, which is an error.

Here's another example of input that causes the code to fail:

```<code>>>> parse_input('{}')
{}
</code>```

This happens because in json_dict = json.loads(json_str), you're overwriting json_dict, so that now it may no longer contain the 'input' key. In my example, json_dict is empty.

Then your code checks if 'input' in json_dict:. No, an empty dictionary doesn't have the 'input' key, so your code proceeds to treat json_dict as... valid??? Even though it still doesn't contain the 'input' key?

In code:

```<code>json_dict = json.loads(json_str)
if 'input' in json_dict:
    json_dict['input'] = json.loads(json_dict['input'])
else:
    ...
    # What if `'input' not in json_dict`?
    # Then `json_dict` remains invalid
</code>```

Body:
def parse_input(json_str):
    json_dict = {'input': []}
    try:
        json_dict = json.loads(json_str)
        if 'input' in json_dict:
            json_dict['input'] = json.loads(json_dict['input'])
    except JSONDecodeError as e:
        print(f'Invalid json str format: {json_str} {e}')
    except TypeError as e:
        print(f'json_str is not a string: {json_str} {e}')

    return json_dict

This function is to parse json string and ALWAYS produce a valid dict object, even if an exception occurs. However, when I process a batch of json strings, it still fails on one of the json strings, and the error is below:

json_str is not a string: null argument of type 'NoneType' is not iterable
Traceback (most recent call last):
  File "kafka_consumer.py", line 76, in <module>
    fetch_data(sys.argv[1])
  File "kafka_consumer.py", line 55, in fetch_data
    for input in json_dict['input']:
TypeError: 'NoneType' object is not subscriptable

The code produced the error above:

        json_dict = parse_input(data)
        for input in json_dict['input']:
            unix_time = input['time']
            ...

So the question is, why doesn't the parse_input() produce a valid dict when the exception occurs? Is my way of using the exception capturing wrong?

Body:
This exact error message happens when data == 'null':

>>> json_dict = parse_input('null')
json_str is not a string: null argument of type 'NoneType' is not iterable
>>> type(json_dict)
<class 'NoneType'>

In this case:

json.loads('null') returns None, so that json_dict is None

'input' in json_dict attempts to iterate over None, but that's an error

Python (not the JSON library!) thus throws the TypeError

Your code handles it and runs print(f'json_str is not a string: {json_str} {e}'). However, the error happened not because json_str wasn't a string, since 'null' is a (Python) string.

Finally, json_dict['input'] attempts to index into the None value, which is an error as well.

Similar behavior can happen when data == '{"input": "null"}':

>>> parse_input('{"input": "null"}')
{'input': None}

This is caused by this line: json_dict['input'] = json.loads(json_dict['input']). It parses the string "null" as the Python value None which for input in json_dict['input'] attempts to iterate over, but that's basically for input in None, which is an error.

Here's another example of input that causes the code to fail:

>>> parse_input('{}')
{}

This happens because in json_dict = json.loads(json_str), you're overwriting json_dict, so that now it may no longer contain the 'input' key. In my example, json_dict is empty.

Then your code checks if 'input' in json_dict:. No, an empty dictionary doesn't have the 'input' key, so your code proceeds to treat json_dict as... valid??? Even though it still doesn't contain the 'input' key?

In code:

json_dict = json.loads(json_str)
if 'input' in json_dict:
    json_dict['input'] = json.loads(json_dict['input'])
else:
    ...
    # What if `'input' not in json_dict`?
    # Then `json_dict` remains invalid


问:
XSLT: how to select all nodes except first
Simplified, my XML is

```<code><some_XML>
  <field-list>
    <field linked="False">
      <content>words</content>
    </field>
    <field linked="False">
      <content>more words</content>
    </field>
      <field linked="True">
      <content>first</content>
    </field>
    <field linked="True">
      <content>second</content>
    </field>
    <field linked="True">
      <content>yet more words.</content>
    </field>
  </field-list>
</some_XML>
</code>```

Linked fields may appear anywhere in the field-list, sometimes the first linked field is the second node, other times it's the 12th, still other times it's another.

My XSLT is

```<code><?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>

  <xsl:template match="some_XML">
    <html>
    <p>
    <xsl:apply-templates select="//field-list" />
    closing words
    </p>
    </html>
  </xsl:template>

  <xsl:template match="field-list">
    <xsl:apply-templates select="field[@linked='True']" /><br/>
  </xsl:template>

  <xsl:template match="field[@linked='True']">
    and <xsl:value-of select="." /><br/>
  </xsl:template>
</xsl:stylesheet>
</code>```

I need it to skip the first "and", so desired output is:

```<code>first
and second
and yet more words.

closing words
</code>```

So far, I've learned that I can select the first node only using
<xsl:apply-templates select="field[@linked='True'][1]" />

but then I need to apply a different template on all subsequent matching nodes, but not on the first. How do I do that?

I think position() won't do because the first linked node may be in any position. Perhaps something with following-sibling could work, but I have a hard time understanding the syntax, that is, I can't seem to find an example close enough to what I need.

Processor is XSLT 1.0.

答: Given that you explicitly select <xsl:apply-templates select="field[@linked='True']" /> for processing, in your template <xsl:template match="field[@linked='True']"> you can of course use e.g. <xsl:if test="position() > 1"> and </xsl:if> to output and only for the second, third and so on processed field[@linked='True'].

Body:
Simplified, my XML is

<some_XML>
  <field-list>
    <field linked="False">
      <content>words</content>
    </field>
    <field linked="False">
      <content>more words</content>
    </field>
      <field linked="True">
      <content>first</content>
    </field>
    <field linked="True">
      <content>second</content>
    </field>
    <field linked="True">
      <content>yet more words.</content>
    </field>
  </field-list>
</some_XML>

Linked fields may appear anywhere in the field-list, sometimes the first linked field is the second node, other times it's the 12th, still other times it's another.

My XSLT is

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>

  <xsl:template match="some_XML">

    <xsl:apply-templates select="//field-list" />
    closing words

  </xsl:template>

  <xsl:template match="field-list">
    <xsl:apply-templates select="field[@linked='True']" />

  </xsl:template>

  <xsl:template match="field[@linked='True']">
    and <xsl:value-of select="." />

  </xsl:template>
</xsl:stylesheet>

I need it to skip the first "and", so desired output is:

first
and second
and yet more words.

closing words

So far, I've learned that I can select the first node only using
<xsl:apply-templates select="field[@linked='True'][1]" />

but then I need to apply a different template on all subsequent matching nodes, but not on the first. How do I do that?

I think position() won't do because the first linked node may be in any position. Perhaps something with following-sibling could work, but I have a hard time understanding the syntax, that is, I can't seem to find an example close enough to what I need.

Processor is XSLT 1.0.


问:
How can I balance "Pythonic" and "convenient" in this case?
I have an "interface" that will be implemented by client code:

```<code>class Runner:
    def run(self):
        pass
</code>```

run should in general return a docutils node but because the far far most
common case is plain text, the caller allows run to return a string, which will be
checked using type() and turned into a node.

However, the way I understand "Pythonic", this is not "Pythonic" because
checking the type() of something doesn't let it "be" a type by "acting" like
one -- ie "Pythonic" code should use duck typing.

I considered

```<code>def run_str(self):
    pass

def run_node(self):
    return make_node(self.run_str())
</code>```

but I don't care for this because it puts the not-so-interesting return type
right there in the name; it's distracting.

Are there any ideas I've missed? Also, are there problems I might encounter
down the road with my "bad" system (it seems more or less safe to me)?

答:
I think this is a slightly deceptive example; there's something you haven't stated. I'm guessing that when you say you "have an interface," what you mean is that you have some code that accepts an object and calls its run method.

If you aren't testing for the type of that object before calling its run method, the you're using duck typing, plain and simple! (In this case, if it has a run method, then it's a Runner.) As long as you don't use type or isinstance on the object with a run method, then you're being Pythonic.

The question of whether you should accept plain strings or only node objects is a subtly different question. Strings and node objects probably don't implement the same interface at all! Strings fundamentally don't quack like a node, so you don't have to treat them like one. This is like an elephant that comes along, and if you want it to quack like a duck, you have to give the elephant a tape player and train the elephant to use it first.

So this isn't a matter of "duck typing" any more, but of interface design. You're trying to decide how strict you want your interface to be.

To give you an answer, then, at this level, I think it's most Pythonic to assume that run returns a node object. There's no need to use isinstance or type to test for that. Just pretend it's a node object and if the programmer using your interface gets that wrong, and sees an exception, then they'll have to read your docstring, which will tell them that run should pass a node object.

Then, if you want to also accept strings, or things that quack like strings, you can do so. And since strings are rather primitive types, I would say it's not inappropriate to use isinstance(obj, basestring) (but not type(obj) == str because that rejects unicode strings, etc.). Essentially, this is you being very liberal and kind to lazy users of your program; you're already going above and beyond by accepting elephants as well as things that quack like ducks.

(More concretely, I'd say this is a bit like calling iter on an argument at the beginning of a function that you want to accept both generators and sequences.)

Body:
I have an "interface" that will be implemented by client code:

class Runner:
    def run(self):
        pass

run should in general return a docutils node but because the far far most
common case is plain text, the caller allows run to return a string, which will be
checked using type() and turned into a node.

However, the way I understand "Pythonic", this is not "Pythonic" because
checking the type() of something doesn't let it "be" a type by "acting" like
one -- ie "Pythonic" code should use duck typing.

I considered

def run_str(self):
    pass

def run_node(self):
    return make_node(self.run_str())

but I don't care for this because it puts the not-so-interesting return type
right there in the name; it's distracting.

Are there any ideas I've missed? Also, are there problems I might encounter
down the road with my "bad" system (it seems more or less safe to me)?


问:
How to calculate relative frequency of array elements using Ramda.js?
I have an array with repeating values, and I want to get the relative frequency (i.e., proportions) for each repeating value.

It seems natural to me to treat this as a 2-step procedure:

count the occurrences of each value; and

divide that count by the length of the original array.

To accomplish the first step we can use R.countBy() from ramda.js:

const R = require("ramda")

const myLetters = ["a", "a", "a", "a", "b", "b", "c", "c", "c", "c"]
const counted = R.countBy(R.identity)(myLetters)
counted // => gives {"a": 4, "b": 2, "c": 4}
```
<p>Now the second step would be to divide <code>counted</code> by the length of <code>myLetters</code>:</p>
<pre class="lang-js prettyprint-override"><code>counted / R.length(myLetters) // obviously this doesn't work because it's not mapped
</code>```

I'm a bit lost with how to map this correctly. My current clunky solution that I dislike:

// 1. manually calculate the length and store to a variable
const nvals = R.length(myLetters)

// 2. create a custom division function
const divide_by_length = (x) => R.divide(x, nvals)

// 3. map custom function to `counted`
R.map(divide_by_length, counted) // gives {"a": 0.4, "b": 0.2, "c": 0.4}
```

Although this works, there's gotta be a more straightforward way with ramda to get from counted to {"a": 0.4, "b": 0.2, "c": 0.4}.

答:
I think that @Scott's answer (2nd solution) caused some pennies to drop for me.

If we define two helper functions preemptively for counting and for dividing:

const myCount = R.countBy(R.identity);
const myDivideBy = (divisor, arr) => R.map(elem => elem / divisor); // I was missing this part
```
<p>Then we could do:</p>
<pre class="lang-js prettyprint-override"><code>const calcFreq = (arr) => {
    return R.pipe(myCount, myDivideBy(arr.length))(arr)
}
</code>```

Which is exactly the 2-step procedure I imagined from the beginning: first count, then divide.

calcFreq(myLetters) // gives {"a": 0.4, "b": 0.2, "c": 0.4}
```

A relevant post: javascript: efficient way to divide an array by a value

Body:
I have an array with repeating values, and I want to get the relative frequency (i.e., proportions) for each repeating value.

It seems natural to me to treat this as a 2-step procedure:

count the occurrences of each value; and

divide that count by the length of the original array.

To accomplish the first step we can use R.countBy() from ramda.js:

const R = require("ramda")

const myLetters = ["a", "a", "a", "a", "b", "b", "c", "c", "c", "c"]
const counted = R.countBy(R.identity)(myLetters)
counted // => gives {"a": 4, "b": 2, "c": 4}

Now the second step would be to divide counted by the length of myLetters:

counted / R.length(myLetters) // obviously this doesn't work because it's not mapped

I'm a bit lost with how to map this correctly. My current clunky solution that I dislike:

// 1. manually calculate the length and store to a variable
const nvals = R.length(myLetters)

// 2. create a custom division function
const divide_by_length = (x) => R.divide(x, nvals)

// 3. map custom function to `counted`
R.map(divide_by_length, counted) // gives {"a": 0.4, "b": 0.2, "c": 0.4}

Although this works, there's gotta be a more straightforward way with ramda to get from counted to {"a": 0.4, "b": 0.2, "c": 0.4}.

Body:
I think that @Scott's answer (2nd solution) caused some pennies to drop for me.

If we define two helper functions preemptively for counting and for dividing:

const myCount = R.countBy(R.identity);
const myDivideBy = (divisor, arr) => R.map(elem => elem / divisor); // I was missing this part

Then we could do:

const calcFreq = (arr) => {
    return R.pipe(myCount, myDivideBy(arr.length))(arr)
}

Which is exactly the 2-step procedure I imagined from the beginning: first count, then divide.

calcFreq(myLetters) // gives {"a": 0.4, "b": 0.2, "c": 0.4}

A relevant post: javascript: efficient way to divide an array by a value


问:
Composer PHPSpreadsheet gives error for gd extension eventhough its enabled
PHP Version 5.6.25
Extension enabled PHP_Gd2

Downloaded PHPSpreadsheet-develop zip file from GitHub.
extracted this file to the project folder c:\wamp\project.

While running below command from the project folder:

```<code>composer require phpoffice/phpspreadsheet
</code>```

Getting following error:
Can someone help me with the error below?

```<code>Using version ^1.2 for phpoffice/phpspreadsheet
./composer.json has been created
Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

Problem 1
- phpoffice/phpspreadsheet 1.2.1 requires ext-gd * -> the requested PHP
extension gd is missing from your system.
- phpoffice/phpspreadsheet 1.2.0 requires ext-gd * -> the requested PHP
extension gd is missing from your system.
- Installation request for phpoffice/phpspreadsheet ^1.2 -> satisfiable by
phpoffice/phpspreadsheet[1.2.0, 1.2.1].

To enable extensions, verify that they are enabled in your .ini files:
- C:\php\php.ini
You can also run php --ini inside terminal to see which files are used by
PHP in CLI mode.

Installation failed, deleting ./composer.json.
</code>```

I have verified that php gd2 extension is enabled as follows:

to verify whether gd2 extension is enabled I manually checked php.ini file windows extension section and it shows that gd2 extension is enabled. "extension=php_gd2.dll".

Checked using phpinfo() it shows that gd2 extension is enabled

The most surprising thing is same composer command run on 1 PC where PHP version is 5.6.35 and giving error as above in another PC where PHP version is 5.6.25. Does it have to do anything with version? As per requirement of PHPSpreadsheet it should work for PHP version 5.6 or newer.

答: Add extension=php_gd.dll to your php.ini file.</p>

Body:
PHP Version 5.6.25
Extension enabled PHP_Gd2

Downloaded PHPSpreadsheet-develop zip file from GitHub.
extracted this file to the project folder c:\wamp\project.

While running below command from the project folder:

composer require phpoffice/phpspreadsheet

Getting following error:
Can someone help me with the error below?

Using version ^1.2 for phpoffice/phpspreadsheet
./composer.json has been created
Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

Problem 1
- phpoffice/phpspreadsheet 1.2.1 requires ext-gd * -> the requested PHP
extension gd is missing from your system.
- phpoffice/phpspreadsheet 1.2.0 requires ext-gd * -> the requested PHP
extension gd is missing from your system.
- Installation request for phpoffice/phpspreadsheet ^1.2 -> satisfiable by
phpoffice/phpspreadsheet[1.2.0, 1.2.1].

To enable extensions, verify that they are enabled in your .ini files:
- C:\php\php.ini
You can also run php --ini inside terminal to see which files are used by
PHP in CLI mode.

Installation failed, deleting ./composer.json.

I have verified that php gd2 extension is enabled as follows:

to verify whether gd2 extension is enabled I manually checked php.ini file windows extension section and it shows that gd2 extension is enabled. "extension=php_gd2.dll".

Checked using phpinfo() it shows that gd2 extension is enabled

The most surprising thing is same composer command run on 1 PC where PHP version is 5.6.35 and giving error as above in another PC where PHP version is 5.6.25. Does it have to do anything with version? As per requirement of PHPSpreadsheet it should work for PHP version 5.6 or newer.


答:
Based on what the cursor appears to be doing, the below cte query should get your results. It appears the cursor is inserting the first MsgLinkId record (MyRowID = 1) and then updating the EndDate for any subsequent record. This query takes the first MsgLinkId record and joins to the last MsgLinkId record in order to take the EndDate, but retains all the other data from the first record.</p>
```<code>;with cte as (
    SELECT MsgLinkId,
          MyRowId,
          row_number() over(partition by MsgLinkId order by MyRowId desc) as MyRowIdDesc,
          WO_ID,
          SourceUnitName,
          MaterialName,
          BatchId,
          UnitofMeasure,
          Segment1,
          Segment2,
          Segment3,
          Segment4,
          Segment5,
          Amount,
          StartDate,
          EndDate
    FROM #AuxTable --- Result of Aux Table
)
select
  row_first.Unit_WO,
  row_first.SourceUnitName,
  row_first.MaterialName,
  row_first.BatchId,
  row_first.UnitofMeasure,
  row_first.Segment1,
  row_first.Segment2,
  row_first.Segment3,
  row_first.Segment4,
  row_first.Segment5,
  row_first.Amount,
  row_first.StartDate,
  row_last.EndDate --On subsequent rows, only the EndDate appears to be updated
from
    cte row_first
    inner join
    cte row_last on row_first.MsgLinkId = row_last.MsgLinkId and row_last.MyRowIdDesc = 1
where
    row_first.MyRowId = 1 --The @Finaltable is first filled with the initial record
</code>```

Title: Grouping rows over common data in a column and min max start/end date via cursor fetch method

Body:
I have a stored procedure with a couple of input parameters

Input parameters:

@UnitID = 13001,
@WOID = 731000037,732000049

Result set:

AuxTable is not the final intended table, it's just being displayed for debugging/troubleshooting.

MsgLinkId
MyRowId
WO_ID
SourceUnitName
MaterialName
BatchID
UnitofMeasure
Segment1
Segment2
Segment3
Segment4
Segment5
Amount
StartDate
EndDate

5
1
731000037
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
13391.7524414063
2022-01-01 14:22:25.773
2022-01-01 14:24:25.980

5
2
731000037
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
13391.7524414063
2022-01-01 14:22:25.773
2022-01-01 14:26:26.187

5
3
731000037
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
13391.7524414063
2022-01-01 14:22:25.773
2022-01-01 14:28:26.397

7
1
732000049
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
31993.4200439453
2022-01-02 17:12:43.550
2022-01-02 17:14:43.757

7
2
732000049
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
31993.4200439453
2022-01-02 17:12:43.550
2022-01-02 17:16:43.967

7
3
732000049
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
31993.4200439453
2022-01-02 17:12:43.550
2022-01-02 17:18:44.177

3
1
732000049
B1S01C04
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
57417.6939544678
2022-01-02 15:48:17.530
2022-01-02 15:50:17.737

3
2
732000049
B1S01C04
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
57417.6939544678
2022-01-02 15:48:17.530
2022-01-02 15:52:17.947

3
3
732000049
B1S01C04
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
57417.6939544678
2022-01-02 15:48:17.530
2022-01-02 15:54:18.157

FinalTable:

Unit_WO
SourceUnitName
MaterialName
BatchID
UnitofMeasure
Segment1
Segment2
Segment3
Segment4
Segment5
Amount
StartDate
EndDate

731000037
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
13391.75
2022-01-01 14:22:25.773
2022-01-01 14:43:09.060

732000049
B1S01C03
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
31993.42
2022-01-02 17:12:43.550
2022-01-02 17:57:51.643

732000049
B1S01C04
RAW MILK
0
KGM
B1S01W32
NONE
NONE
NONE
NONE
57417.7
2022-01-02 15:48:17.530
2022-01-02 17:12:40.333

This is just a sample to understand the situation.

Question: how can I get "FinalTable", without using the cursor fetch method?

I have tried using max(Amount), min(Start date), max(End date) and tried grouping over MsgLinkId,MyRowId but it didn't work.

This is my stored procedure:

CREATE PROCEDURE dbo.********
    @UnitID nvarchar(50),
    @WOID nvarchar(50),
    @JobId nvarchar(50)= '',
    @StartDate datetime = '',
    @EndDate datetime = ''
AS
BEGIN
    IF @EndDate = ''
    BEGIN
        SET @EndDate = @StartDate
    END

    SET @unitID = REPLACE(@unitId, 'All', '')
    SET @WOID = REPLACE(@WOID, 'All', '')
    SET @JobID = REPLACE(@JobId, 'All', '')
    -- SET @startdate = REPLACE(@startdate, 'All', '')
    -- SET @enddate = REPLACE(@enddate, 'All', '')

    SELECT
        T1.Msg_LinkId AS MsgLinkId,
        ROW_NUMBER () OVER (PARTITION BY T1.Msg_LinkId
                            ORDER BY T2.DT_Msg Asc) MyRowid,
        T1.Unit_WO AS WO_ID,
        T3.Name AS SourceUnitName,
        T4.Name AS MaterialName,
        T1.Data_DInt04 AS BatchId,
        T5.description AS UnitofMeasure,
        T6.Name AS Segment1,
        T7.Name AS Segment2,
        T8.Name AS Segment3,
        T9.Name AS Segment4,
        T10.Name AS Segment5,
        SUM(T2.Data_Real01) OVER (PARTITION BY T2.Msg_LinkId) Amount,
        T1.DT_Msg AS StartDate,
        T2.DT_Msg AS EndDate
    INTO
        #AuxTable
    FROM
        QHandler_Messages T1
    LEFT JOIN
        QHandler_Messages T2 ON T1.Unit_WO = T2.Unit_WO
                             AND T1.Msg_LinkId = T2.Msg_LinkId
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T3 ON T1.Data_DInt02 = T3.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.vi_Materials T4 ON T1.Data_DInt03 = T4.Material_ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.vw_uoms_all T5 ON T1.Data_DInt05 = T5.uom_id
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T6 ON T1.Data_DInt06 = T6.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T7 ON T1.Data_DInt07 = T7.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T8 ON T1.Data_DInt08 =   T8.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T9 ON T1.Data_DInt09 = T9.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T10 ON T1.Data_DInt10 = T10.ID
    WHERE
        (T1.unit_wo IN (SELECT CAST(splitdata AS nvarchar(max))
                        FROM dbo.ft_Admin_SplitString(@WOID, ','))
         OR @WOID = '')
        AND (T1.unit_id IN (SELECT CAST(splitdata AS nvarchar(max))
                            FROM dbo.ft_Admin_SplitString(@UnitID, ','))
             OR @unitid = '')
        AND T1.Msg_Identifier = 11
        AND T2.Msg_Identifier = 12
        AND (T1.Data_DInt01 IN (SELECT CAST(splitdata AS nvarchar(max))
                                FROM dbo.ft_Admin_SplitString(@JobID, ','))
             OR @jobid = '')

    UNION ALL

    SELECT
        T1.Msg_LinkId as MsgLinkId,
        ROW_NUMBER () OVER (PARTITION BY T1.Msg_LinkId
                            ORDER BY T1.DT_Msg Asc) MyRowid,
        T1.Unit_WO AS WO_ID,
        T3.Name AS SourceUnitName,
        T4.Name AS MaterialName,
        T1.Data_DInt04 AS BatchId,
        T5.description AS UnitofMeasure,
        T6.Name AS Segment1,
        T7.Name AS Segment2,
        T8.Name AS Segment3,
        T9.Name AS Segment4,
        T10.Name AS Segment5,
        SUM(T1.Data_Real01) OVER (PARTITION BY T1.Msg_LinkId)   Amount,
        T1.DT_Msg AS StartDate,
        T1.DT_Msg AS EndDate
    FROM
        QHandler_Messages T1
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T3 ON T1.Data_DInt02 = T3.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.vi_Materials T4 ON T1.Data_DInt03 = T4.Material_ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.vw_uoms_all T5 ON T1.Data_DInt05 = T5.uom_id
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T6 ON T1.Data_DInt06 = T6.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T7 ON T1.Data_DInt07 = T7.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T8 ON T1.Data_DInt08 = T8.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T9 ON T1.Data_DInt09 = T9.ID
    LEFT JOIN
        LK_CUSTOMDB.dbo.ListUnit T10 ON T1.Data_DInt10 = T10.ID
    WHERE
        (T1.unit_wo IN (SELECT CAST(splitdata AS nvarchar(max))
                        FROM dbo.ft_Admin_SplitString(@WOID, ','))
         OR @WOID = '')
        AND (T1.unit_id IN (SELECT CAST(splitdata AS nvarchar(max))
                            FROM dbo.ft_Admin_SplitString(@UnitID, ','))
             OR @unitid = '')
        AND T1.Msg_Identifier = 13
        AND (T1.Data_DInt01 IN (SELECT CAST(splitdata AS nvarchar(max))
                                FROM dbo.ft_Admin_SplitString(@JobID, ','))
             OR @jobid = '')
        AND T1.DT_msg BETWEEN @StartDate
        AND DATEADD(HOUR, 48, @EndDate)

    SELECT *
    FROM #AuxTable --- Result of Aux Table

    DECLARE @Finaltable TABLE
                        (
                            MsgLinkId int,
                            Unit_WO bigint,
                            SourceUnitName nvarchar (255),
                            MaterialName nvarchar (255),
                            BatchId bigint,
                            UnitofMeasure nvarchar (255),
                            Segment1 nvarchar (255),
                            Segment2 nvarchar (255),
                            Segment3 nvarchar (255),
                            Segment4 nvarchar (255),
                            Segment5 nvarchar (255),
                            Amount real,
                            StartDate Datetime,
                            EndDate Datetime
                        )
    DECLARE @MsgLinkId int
    DECLARE @MyRowId int DECLARE cursorAux cursor for
select
  MsgLinkId,
  MyRowid
from
  #Auxtable
  open cursorAux FETCH NEXT
FROM
  cursorAux INTO @MsgLinkId,
  @MyRowid WHILE @@FETCH_STATUS = 0 BEGIN If Exists (
    select
      *
    from
      @Finaltable
    where
      MsgLinkId = @MsgLinkId
  ) Begin
Update
  @Finaltable
Set
  EndDate = (
    Select
      EndDate
    from
      #Auxtable where MsgLinkId = @MsgLinkId and MyRowid = @MyRowid)
    Where
      MsgLinkId = @MsgLinkId end ELSE Begin Insert into @Finaltable (
        MsgLinkId, Unit_WO, SourceUnitName,
        MaterialName, BatchId, UnitofMeasure,
        Segment1, Segment2, Segment3, Segment4,
        Segment5, Amount, StartDate, EndDate
      )
    select
      MsgLinkId,
      WO_ID,
      SourceUnitName,
      MaterialName,
      BatchId,
      UnitofMeasure,
      Segment1,
      Segment2,
      Segment3,
      Segment4,
      Segment5,
      Amount,
      StartDate,
      EndDate
    from
      #Auxtable
    where
      MsgLinkId = @MsgLinkId
      and MyRowid = @MyRowid end FETCH NEXT
    FROM
      cursorAux INTO @MsgLinkId,
      @MyRowid END CLOSE cursorAux;
DEALLOCATE cursorAux;
select
  Unit_WO,
  SourceUnitName,
  MaterialName,
  BatchId,
  UnitofMeasure,
  Segment1,
  Segment2,
  Segment3,
  Segment4,
  Segment5,
  Amount,
  StartDate,
  EndDate
from
  @Finaltable END GO

Body:
Based on what the cursor appears to be doing, the below cte query should get your results. It appears the cursor is inserting the first MsgLinkId record (MyRowID = 1) and then updating the EndDate for any subsequent record. This query takes the first MsgLinkId record and joins to the last MsgLinkId record in order to take the EndDate, but retains all the other data from the first record.

;with cte as (
    SELECT MsgLinkId,
          MyRowId,
          row_number() over(partition by MsgLinkId order by MyRowId desc) as MyRowIdDesc,
          WO_ID,
          SourceUnitName,
          MaterialName,
          BatchId,
          UnitofMeasure,
          Segment1,
          Segment2,
          Segment3,
          Segment4,
          Segment5,
          Amount,
          StartDate,
          EndDate
    FROM #AuxTable --- Result of Aux Table
)
select
  row_first.Unit_WO,
  row_first.SourceUnitName,
  row_first.MaterialName,
  row_first.BatchId,
  row_first.UnitofMeasure,
  row_first.Segment1,
  row_first.Segment2,
  row_first.Segment3,
  row_first.Segment4,
  row_first.Segment5,
  row_first.Amount,
  row_first.StartDate,
  row_last.EndDate --On subsequent rows, only the EndDate appears to be updated
from
    cte row_first
    inner join
    cte row_last on row_first.MsgLinkId = row_last.MsgLinkId and row_last.MyRowIdDesc = 1
where
    row_first.MyRowId = 1 --The @Finaltable is first filled with the initial record


问:
Fast method of getting all the descendants of a parent
With the parent-child relationships data frame as below:

```<code>  parent_id child_id
1         1        2
2         2        3
3         3        4
</code>```

The goal is to achieve the following, i.e. expanded version of previous data frame where all the descendants (children, grandchildren, etc.) are assigned to each parent (and incl. the parent/child itself):

```<code>   parent_id child_id
1          1        1
2          1        2
3          1        3
4          1        4
5          2        2
6          2        3
7          2        4
8          3        3
9          3        4
10         4        4
</code>```

The question I have: What is the fastest possible way (or one of them) of achieving that in R?

I've already tried various methods - from a for loop, SQL recursion to using igraph (as described here). They are all rather slow, and some of them are also prone to crashing when dealing with a larger number of combinations.

Below are examples with sqldf and igraph, benchmarked on a slightly larger data frame than above.

```<code>library(sqldf)
library(purrr)
library(dplyr)
library(igraph)

df <- data.frame(parent_id = 1:1000L)
df$child_id <- df$parent_id + 1L

# SQL recursion

sqlQuery <- 'with recursive
             dfDescendants (parent_id, child_id)
             as
             (select parent_id, child_id from df
             union all
             select d.parent_id, s.child_id from dfDescendants d
             join df s
             on d.child_id = s.parent_id)
             select distinct parent_id, parent_id as child_id from dfDescendants
             union
             select distinct child_id as parent_id, child_id from dfDescendants
             union
             select * from dfDescendants;'

sqldf(sqlQuery)

# igraph with purrr

df_g = graph_from_data_frame(df, directed = TRUE)

map(V(df_g), ~ names(subcomponent(df_g, .x, mode = "out"))) %>%
  map_df(~ data.frame(child_id = .x), .id = "parent_id")
</code>```

Benchmark (excl. query creation in sqldf and conversion to graph in igraph):

```<code>set.seed(23423)

microbenchmark::microbenchmark(
  sqldf = sqldf(sqlQuery),
  tidyigraph = map(V(df_g), ~ names(subcomponent(df_g, .x, mode = "out"))) %>%
    map_df(~ data.frame(child_id = .x), .id = "parent_id"),
  times = 5
)

#    Unit: seconds
#           expr      min       lq     mean   median       uq      max neval
#          sqldf 7.815179 8.002836 8.113392 8.084038 8.315207 8.349701     5
#     tidyigraph 5.784239 5.806539 5.883241 5.889171 5.964906 5.971350     5
</code>```

答:
We can use ego like below

```<code>library(igraph)
df <- data.frame(parent_id = 1:3, child_id = 2:4)
g <- graph_from_data_frame(df)

setNames(
  rev(
    stack(
      Map(
        names,
        setNames(
          ego(g,
            order = vcount(g),
            mode = "out"
          ),
          names(V(g))
        )
      )
    )
  ),
  names(df)
)
</code>```

which gives

```<code>   parent_id child_id
1          1        1
2          1        2
3          1        3
4          1        4
5          2        2
6          2        3
7          2        4
8          3        3
9          3        4
10         4        4
</code>```

Benchmarking

```<code>set.seed(23423)

microbenchmark::microbenchmark(
  sqldf = sqldf(sqlQuery),
  tidyigraph = map(V(df_g), ~ names(subcomponent(df_g, .x, mode = "out"))) %>%
    map_df(~ data.frame(child_id = .x), .id = "parent_id"),
  ego = setNames(
    rev(
      stack(
        Map(
          names,
          setNames(
            ego(df_g,
              order = vcount(df_g),
              mode = "out"
            ),
            names(V(df_g))
          )
        )
      )
    ),
    names(df)
  ),
  times = 5
)
</code>```

shows

```<code>Unit: milliseconds
       expr       min       lq      mean    median         uq        max neval
      sqldf 7156.2753 9072.155 9402.6904 9518.2796 10206.3683 11060.3738     5
 tidyigraph 2483.9943 2623.558 3136.7490 2689.8388  2879.5688  5006.7853     5
        ego  182.5941  219.151  307.2481  253.2171   325.8721   555.4064     5
</code>```

The code using pipes for readability :

```<code>g |>
  ego(order = vcount(g), mode = "out") |>
  setNames(names(V(g))) |>
  Map(f = names) |>
  stack() |>
  rev() |>
  setNames(names(df))
</code>```

Body:
With the parent-child relationships data frame as below:

  parent_id child_id
1         1        2
2         2        3
3         3        4

The goal is to achieve the following, i.e. expanded version of previous data frame where all the descendants (children, grandchildren, etc.) are assigned to each parent (and incl. the parent/child itself):

   parent_id child_id
1          1        1
2          1        2
3          1        3
4          1        4
5          2        2
6          2        3
7          2        4
8          3        3
9          3        4
10         4        4

The question I have: What is the fastest possible way (or one of them) of achieving that in R?

I've already tried various methods - from a for loop, SQL recursion to using igraph (as described here). They are all rather slow, and some of them are also prone to crashing when dealing with a larger number of combinations.

Below are examples with sqldf and igraph, benchmarked on a slightly larger data frame than above.

library(sqldf)
library(purrr)
library(dplyr)
library(igraph)

df <- data.frame(parent_id = 1:1000L)
df$child_id <- df$parent_id + 1L

# SQL recursion

sqlQuery <- 'with recursive
             dfDescendants (parent_id, child_id)
             as
             (select parent_id, child_id from df
             union all
             select d.parent_id, s.child_id from dfDescendants d
             join df s
             on d.child_id = s.parent_id)
             select distinct parent_id, parent_id as child_id from dfDescendants
             union
             select distinct child_id as parent_id, child_id from dfDescendants
             union
             select * from dfDescendants;'

sqldf(sqlQuery)

# igraph with purrr

df_g = graph_from_data_frame(df, directed = TRUE)

map(V(df_g), ~ names(subcomponent(df_g, .x, mode = "out"))) %>%
  map_df(~ data.frame(child_id = .x), .id = "parent_id")

Benchmark (excl. query creation in sqldf and conversion to graph in igraph):

set.seed(23423)

microbenchmark::microbenchmark(
  sqldf = sqldf(sqlQuery),
  tidyigraph = map(V(df_g), ~ names(subcomponent(df_g, .x, mode = "out"))) %>%
    map_df(~ data.frame(child_id = .x), .id = "parent_id"),
  times = 5
)

#    Unit: seconds
#           expr      min       lq     mean   median       uq      max neval
#          sqldf 7.815179 8.002836 8.113392 8.084038 8.315207 8.349701     5
#     tidyigraph 5.784239 5.806539 5.883241 5.889171 5.964906 5.971350     5

Body:
We can use ego like below

library(igraph)
df <- data.frame(parent_id = 1:3, child_id = 2:4)
g <- graph_from_data_frame(df)

setNames(
  rev(
    stack(
      Map(
        names,
        setNames(
          ego(g,
            order = vcount(g),
            mode = "out"
          ),
          names(V(g))
        )
      )
    )
  ),
  names(df)
)

which gives

   parent_id child_id
1          1        1
2          1        2
3          1        3
4          1        4
5          2        2
6          2        3
7          2        4
8          3        3
9          3        4
10         4        4

Benchmarking

set.seed(23423)

microbenchmark::microbenchmark(
  sqldf = sqldf(sqlQuery),
  tidyigraph = map(V(df_g), ~ names(subcomponent(df_g, .x, mode = "out"))) %>%
    map_df(~ data.frame(child_id = .x), .id = "parent_id"),
  ego = setNames(
    rev(
      stack(
        Map(
          names,
          setNames(
            ego(df_g,
              order = vcount(df_g),
              mode = "out"
            ),
            names(V(df_g))
          )
        )
      )
    ),
    names(df)
  ),
  times = 5
)

shows

Unit: milliseconds
       expr       min       lq      mean    median         uq        max neval
      sqldf 7156.2753 9072.155 9402.6904 9518.2796 10206.3683 11060.3738     5
 tidyigraph 2483.9943 2623.558 3136.7490 2689.8388  2879.5688  5006.7853     5
        ego  182.5941  219.151  307.2481  253.2171   325.8721   555.4064     5

The code using pipes for readability :

g |>
  ego(order = vcount(g), mode = "out") |>
  setNames(names(V(g))) |>
  Map(f = names) |>
  stack() |>
  rev() |>
  setNames(names(df))