一个表中有user_id和middle_user_id两个字段,怎么统计user_id和middle_user_id中的值的出现次数
时间: 2024-10-15 11:08:41 浏览: 26
要统计`user_id`和`middle_user_id`字段中值的出现次数,你可以使用SQL查询来完成这个任务。这通常涉及到对数据库中的数据进行分组计数。假设我们有一个名为`table_name`的表,可以尝试以下SQL语句:
```sql
SELECT user_id, middle_user_id, COUNT(*) as occurrence_count
FROM table_name
GROUP BY user_id, middle_user_id;
```
这条查询会按`user_id`和`middle_user_id`字段的组合对记录进行分组,并计算每个组别的记录数目,即每个值出现的次数。
如果你想要了解特定用户ID下的`middle_user_id`的出现次数,可以稍微调整一下:
```sql
SELECT user_id, middle_user_id, COUNT(middle_user_id) as occurrence_count
FROM table_name
WHERE user_id = 'your_specific_user_id'
GROUP BY middle_user_id;
```
将`'your_specific_user_id'`替换为你关心的具体用户ID。
相关问题
一个表中有user_id和middle_user_id两个字段,user_id和middle_user_id会有重复值也可能不重复,怎么统计user_id和middle_user_id所有出现的值的出现次数
为了统计`user_id`和`middle_user_id`字段的所有组合出现的次数,你可以使用SQL查询来完成这个任务。这里是一个通用的示例,假设你正在使用MySQL数据库:
```sql
SELECT user_id, middle_user_id, COUNT(*) as frequency
FROM your_table_name
GROUP BY user_id, middle_user_id;
```
在这个查询中:
- `your_table_name` 应替换为你实际的数据表名。
- `COUNT(*)` 计算每个组合的行数,即频率。
- `GROUP BY` 子句按`user_id` 和 `middle_user_id` 分组,确保每一对值都有单独的计数。
如果你只想计算非重复的组合(即没有重复的`user_id`和`middle_user_id`),可以去掉`GROUP BY`,改为:
```sql
SELECT user_id, middle_user_id
FROM your_table_name
WHERE user_id AND middle_user_id IS NOT NULL AND user_id <> middle_user_id
UNION ALL
SELECT user_id, user_id AS middle_user_id
FROM your_table_name
WHERE user_id IS NOT NULL;
```
这将分别计算`user_id`和`middle_user_id`相等的情况(可能是同一个用户的不同标识)以及不相等的情况,并合并结果。
这个代码我可以添加一个列表,并且在画图时显示,用户通过一个下拉式的项目栏点击可以确定field_name的值,完整代码
好的,你可以试试下面的代码,我添加了一个列表 `field_name_list`,用户可以通过下拉式的项目栏来选择 `field_name` 的值。代码中还添加了一些注释方便理解。如果有问题可以随时问我。
```python
import os
import math
import json
import operator
import matplotlib.pyplot as plt
import re
from tkinter import *
def get_filenames(folder_path):
"""get filename"""
json_files = []
for file in os.listdir(folder_path):
if file.endswith('.json'):
json_files.append(file)
return json_files
def get_id_velocity(json_files, id_num, field_name):
"""Read the json file to find the value corresponding to the field name"""
x_list=[]
y_list=[]
name_list = []
for file in json_files:
with open(os.path.join(folder_path, file), 'r') as f:
data = json.load(f)
obstacles = data.get('obstacles')
if obstacles:
for obstacle in obstacles:
if obstacle['id']== id_num:
field_value = obstacle.get(field_name)
if field_value == None:
return x_list,y_list,name_list
x_list.append(field_value['x'])
y_list.append(field_value['y'])
name_list.append(file)
return x_list,y_list,name_list
def get_field_name(event):
"""get field name from dropdown list"""
global field_name
field_name = variable.get()
if __name__ == '__main__':
# create tkinter window
root = Tk()
root.title("Display based on user input")
# create dropdown list for field name
field_name_list = ['field_name1', 'field_name2', 'field_name3', 'field_name4']
variable = StringVar(root)
variable.set(field_name_list[0])
variable.trace("w", get_field_name)
w = OptionMenu(root, variable, *field_name_list)
w.pack()
# create plot figure
fig, ax = plt.subplots()
plt.title("display based on user input")
while True:
folder_path = 'front_middle_obs'
id_num = int(input('请输入要显示的id号,输入0退出:'))
if id_num == 0:
print("exit")
break
# get field name from dropdown list
field_name = variable.get()
json_files = get_filenames(folder_path=folder_path)
json_files.sort(key=operator.methodcaller("lower"))
x_list,y_list,name_list=get_id_velocity(json_files=json_files, id_num=id_num, field_name=field_name)
if len(x_list)==0:
print(f'请确定你的输入无误,我没有找到你输入的id:{id_num}对应的字段名:{field_name}')
continue
# calculate velocity
list3 = []
for i in range(len(x_list)):
value = math.sqrt(x_list[i] ** 2 + y_list[i] ** 2)
list3.append(value)
new_name_lst = list(range(len(name_list)))
x=[]
for file_name in name_list:
num = re.findall(r'\d+', file_name)[0]
x.append(num)
# plot curves
ax.plot(new_name_lst, x_list,label='velocity_x',linewidth=2)
ax.plot(new_name_lst, y_list, label='velocity_y',linewidth=2)
ax.plot(new_name_lst, list3, label='velocity',linewidth=2)
# set x-axis label
ax.set_xticks(new_name_lst)
ax.set_xticklabels(x)
# set legend
leg = ax.legend(loc='upper left', fancybox=True, shadow=True)
leg.get_frame().set_alpha(0.5)
# set line picker
lines = [l for l in ax.lines]
lined = dict()
for legline, origline in zip(leg.get_lines(), lines):
legline.set_picker(True)
lined[legline] = origline
def onpick(event):
"""visibility"""
legline = event.artist
origline = lined[legline]
visible = not origline.get_visible()
origline.set_visible(visible)
if visible:
legline.set_alpha(1.0)
else:
legline.set_alpha(0.2)
fig.canvas.draw()
fig.canvas.mpl_connect('pick_event', onpick)
# show plot
plt.show()
# close tkinter window
root.destroy()
```
阅读全文